You can use the following methods to get a row number from a range in Excel using VBA:
Method 1: Get Row Number from Specific Range
Sub GetRowNumber()
rowNum = Range("D7").Row
MsgBox rowNum
End Sub
This particular macro will display a message box with the row number that corresponds to cell D7, which would be 7.
Method 2: Get Row Number from Currently Selected Range
Sub GetRowNumber()
rowNum = Selection.Row
MsgBox rowNum
End Sub
This particular macro will display a message box with the row number that corresponds to the currently selected range in Excel.
For example, if you have cell B3 selected when you run this macro then a message box will appear with the value 3 in it.
The following examples show how to use each method in practice.
Example 1: Get Row Number from Specific Range
Suppose we would like to get the row number for the cell reference D7.
We can create the following macro to do so:
Sub GetRowNumber()
rowNum = Range("D7").Row
MsgBox rowNum
End Sub
When we run this macro, we receive the following output:
The message box displays a value of 7, which is the row number for the cell reference D7.
Example 2: Get Row Number from Currently Selected Range
Suppose we would like to get the row number from the currently active cell.
We can create the following macro to do so:
Sub GetRowNumber()
rowNum = Selection.Row
MsgBox rowNum
End Sub
Suppose we currently have cell B3 selected.
When we run this macro, we receive the following output:
The message box displays a value of 3, which is the row number for the currently active cell of B3.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
VBA: How to Count Number of Rows in Range
VBA: How to Copy Visible Rows to Another Sheet
VBA: How to Highlight Rows
