Community discussion forum

need a VBscript to boldface columns in a spreadsheet

• • Reply

  2 years ago

  by nanay2007

  nanay2007

  , United Kingdom

  Joined 2 years ago

  Hello,

  I have a spreadsheet which contains a block of data which has just numbers. The size of this block is approximately 10963 by 8, so it is very long columnwise. I am trying to use bold-face type in every seventh column of this block.  That is, I want all the cells in every other seventh column to be in bold. If I am going to this manually, it would be time-consuming. However, if I could write a VB script to do this, it would take only a second or two. Could anyone provide me with such a script. Thank you.  

  Report abuse
• • Reply

  2 years ago

  by Kassem

  Kassem Baalbaki

  Beirut, Lebanon

  Joined 3 years ago

  Hi,

  First of all I think you mean that the number of rows is 10963 as the excel sheet accomodates up to 255 columns. Accordingly do the following:

  Make sure that the first row is the header of the sheet (don't leave any empty rows above your table or to the left of it so it will look like this)

  Col 1	Col 2	Col 3	Col 4	Col 5	Col 6	Col 7	Col 8
  59	52	92	62	2	59	16	93
  11	35	76	18	49	76	55	74
  75	100	96	45	33	4	32	65
  26	65	28	48	80	61	36	33
  75	33	64	21	11	65	100	1
  43	85	30	31	43	42	2	90

  where the cell that contains the header value Col 1 is the cell A1 in the excel sheet.

  Now create a button on the excel sheet (view >> toolbars >> control toolbox and choose the button icon and draw it on the form) and again press from the toolbar: Tools >> Macros >> Visual Basic Editor. And there paste the below code:

  Dim RowCount As Long, SeventhRow As Long

  Private Sub CommandButton1_Click()
     
      SeventhRow = 8
      Range("A1").Select
      Selection.End(xlDown).Select
      RowCount = Selection.Row - 1
      Range("A1").Select
      Do While SeventhRow <= RowCount
      Rows(SeventhRow & ":" & SeventhRow).Select
      Selection.Font.Bold = True
      SeventhRow = SeventhRow + 7
      Loop
     
      Range("A1").Select
  
  
  
  
  
  
  
  
  
  
  
  
  

  End Sub
  
  

  Now go back to the excel sheet, exit the design mode and press the button and that is it.

   

  I hope this will work for you.

  Report abuse
• • Reply

  2 years ago

  by nanay2007

  nanay2007

  , United Kingdom

  Joined 2 years ago

  Hi,

  Your are absolutely correct about the size of the cells. I apologize for this mistake. I will try the script

  you sent me. Thank you.

   

  Report abuse
• • Reply

  2 years ago

  by nanay2007

  nanay2007

  , United Kingdom

  Joined 2 years ago

  Hi Kassem

   

  I have tried the script that you sent me, and it works fine.  I was able to move forward with my project, and now I have a new problem.

  Remember that I have a block of numbers that's actually 12 columns by 567 rows. What I want to try with this spreadsheet is to create a chart of XY (scatter) type, and make 81 series of plot on this chart. Each of these 81 series consists of 7 data points ( so that 7 * 81 = 567 ). That is the reason why I wanted to boldface every seventh row of this spreadsheet so that I could better visualize what I want to plot. In each of the series of the plot, the y-axis represent the seventh column, and x-axis represents the first column. Each series plots every seven data points in the order given in the spreadsheet. If I could also create create script that generates this chart, it would save me a lot of time and effort. Do you know how to write this script to plot series of data points from a spreadsheet? Thank you for your help.

  I remember trying to create this sort of script before (using excel macro), but for some reason, it did not work.

  Anyone could also help me with this problem.  Thank you.

   

  Report abuse
• • Reply

  2 years ago

  by Kassem

  Kassem Baalbaki

  Beirut, Lebanon

  Joined 3 years ago

  Hi,

  Back to your first problem I have ammended the code to read :

  Dim RowCount As Long, SeventhRow As Long

  Private Sub CommandButton1_Click()
     
      SeventhRow = 8
      Range("A1").Select
      Selection.End(xlDown).Select
      RowCount = Selection.Row - 1
      Range("A1").Select
      Do While SeventhRow <= RowCount
      Rows(SeventhRow & ":" & SeventhRow).Font.Bold = True
      SeventhRow = SeventhRow + 7
      Loop
     
      Range("A1").Select
  
  
  
  
  
  
  
  
  
  
  
  

  
  End Sub
  

   

  Which is much faster and will not freeze the sheet during prcessing.

  As for the new problem kindly forward me a copy of your excel file with a sample chart as I wasn't able to understand your quest clearly. My email is kbaalbakixxx@gmail.com. Remove the extra "xxx" from the email before using it.

  Report abuse