VBA: How to Get Row Number from Range


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

Featured Posts

Leave a Reply

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