VBA range.row

The Range.Row property returns the number of the first row in the selected range.

To illustrate you this we are going to select the following range in VBA code.

Run this code.

Sub DisplayFirstRow()
    Set myRange = Range("B2:C7")
    MsgBox myRange.Row
End Sub

Or even simpler code.

Sub DisplayFirstRow()
    MsgBox ActiveSheet.Range("B2:C7").Row
End Sub

It’s going to display the following message.

The first row inside our range (B2:C7) is 2 and VBA returns this value in a MsgBox.

Highlight every other row

In this example, we are going to use the for loop to highlight the even rows in the selected range.

Sub HighlightEvenRows()
    For Each myRow In Range("B2:C7").Rows
        If myRow.Row Mod 2 = 0 Then
            myRow.Interior.ThemeColor = xlThemeColorAccent6
        End If
    Next myRow
End Sub

Code explanation:

2. The for loop is used to go through each row inside the selected range (B2:C7).

3. If myRow.Row Mod 2 equals 0 (the remainder after the division of one number by another) then execute the next line of code.

4. This line of code fills the cell with the theme color.

The first row (2) is even, so Excel will start highlighting from this row.

Let’s modify our code in order to add row and column numbers.

Sub ShowRowAndColumnNumber()
    For Each cell In Range("B2:C7")
        cell.Value = cell.Row & "," & cell.Column
        If cell.Row Mod 2 = 0 Then
            cell.Interior.ThemeColor = xlThemeColorAccent6
        End If
    Next cell
End Sub

Code explanation:

2. We had to modify the For loop. Now, we want to loop through each cell, not each row. That’s why there is no Rows property in the loop.

3. This line of code assigns the cell row and cell column numbers to cell value.

Posted in vba