Refer Rows Columns Excel VBA

Refer to rows and columns in Excel using macro

Implementing the macro:

  • Click on tools in the menu bar, select Macro and from the options select Visual Basic Editor
  • In the VBA editor window click on the insert menu and select module
  • Now enter the following code:

Sub MakeRowBold()

Worksheets(“Sheet1”).Rows(3).Font.Bold = True

End Sub

  • You can also use the shortened code:

Sub MakeRowBold()

Sheet1.Rows(3).Font.Bold = True

End Sub

  • Click on run ‘>’ button or press F5 from the keyboard to execute the macro
  • The data in row 3 gets highlighted (bold).
  • If you wish to work with several different rows at the same time you can use the code:

Sub SeveralRows()

Worksheets(“Sheet1”).Activate

Dim myUnion As Range

Set myUnion = Union(Rows(4), Rows(8), Rows(9))

myUnion.Font.Bold = True

End Sub

  • You can highlight a column using the macro code:

sub colorColumn()

Worksheets(“Sheet1”).Columns(4).Font.Colorindex=3

End Sub

  • After defining the name the code activates the sheet 1
  • Then we define a variable object using Dim (short for dimension) with the name myUnion and data type as range
  • Then we include or set the rows defined by myUnion
  • When you type myUnion and a ‘.’ after it you automatically get the options like Font when you type ‘fo’ after the dot. Select Font from the options. Then you type a ‘.’ after Font to get more options like ‘Bold’.
  • Select bold.
  • Set the myunion.font.bold as true and all data in the rows becomes bold once you run the macro by pressing the ‘>’ run button or the F5 key from the keyboard. Try it out!

Watch the Excel training video below to see the first macro in action:

Background info: You can use the Excel Rows property or the Excel Columns property to work with rows or columns in Excel. These properties return a Range object that represents a range of Excel cells. In the following example, Rows(1) returns row one on Excel Sheet1. The Bold property of the Font object for the Excel range is then set to True.

Sub MakeRowBold()

Worksheets(“Sheet1”).Rows(1).Font.Bold = True

End Sub

The following table illustrates some row and column references using the Rows and Columns properties in Microsoft Excel.

Reference                                                             Meaning

Rows(1)                                                                Row one

Rows                                                                     All the rows

Columns(1)                                                          Columns(“A”)

Columns                                                               All the columns

If you wish to work with several rows or columns at the same time in Excel, create an object variable and use the Union method, combining multiple calls to the Rows or Columns property. The following example changes the format of rows one, three, and five on Excel worksheet one in the active Excel workbook to bold.

Sub SeveralRows()

Worksheets(“Sheet1”).Activate

Dim myUnion As Range

Set myUnion = Union(Rows(1), Rows(3), Rows(5))

myUnion.Font.Bold = True

End Sub

 

Further reading:

Sample macro to insert/delete rows or columns on multiple sheets in Excel

Leave a Reply

Your email address will not be published. Required fields are marked *