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.
|
1 2 3 4 |
Sub DisplayFirstRow() Set myRange = Range("B2:C7") MsgBox myRange.Row End Sub |
Or even simpler code.
|
1 2 3 |
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.
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 4 5 6 7 8 |
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.