Excel Training Online 

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 1 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
Rows(1)
Rows
Columns(1)
Columns("A")
Columns
Meaning
Row one
All the rows on the Excel worksheet
Column one
Column one
All the columns on the Excel worksheet
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

Reference

How to refer to rows and columns using a macro in Excel