Table of Contents
This article will teach you how to use Visual Basic for Applications (VBA) to quickly and easily identify the row number of a given range. You will learn how to use the Rows.Count property to retrieve the row number of a given range. You will also learn how to use the Range.Row property to extract the row number of a single cell. By the end of this article, you will be able to use VBA to identify the row number of any 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
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.