How to Get Row Number from Range with Excel VBA (9 Methods)

Method 1 – Get Row Number from Range

Steps

• Open the VBA window by going to the Developer tab and selecting Visual Basic.

• Insert a new module.

• Enter the following code in the module:

Sub GetRowNumber()
    rowNumber = Range("B4").row
    MsgBox "Here,Row Number is: " & rowNumber
End Sub

• Save the code.
• Close the Visual Basic window.
• Press Alt+F8 to open the Macro dialog box.
• Select GetRowNumber from the list of macros and click Run.

• You’ll see a message box displaying the row number of cell B4.

Sub GetRowNumber()- Provides a name for the sub-procedure of the macro.

rowNumber = Range("B4").row - This variable will contain the number of rows of the range.

MsgBox "Here,Row Number is: " & rowNumber - A message is displayed in a dialog box along with a row number.

End Sub - Ends the sub-procedure of the macro.

Read More: How to Find Row Number Using VBA in Excel

Method 2 – Get Row Number from a Selection

Steps

• Open the VBA window by navigating to the Developer tab and selecting Visual Basic.

• Insert a new module.

• Enter the following code in the module:

Sub From_Range()
   Dim Row_First As Long
   Dim Row_Last As Long
   Row_First = Selection.Row
   Row_Last = Selection.Row + Selection.Rows.Count - 1
   MsgBox Row_First & " to " & Row_Last
End Sub

• Save the code.
• Close the Visual Basic window.
• Press Alt+F8 to open the Macro dialog box.
• Select From_Range from the list of macros and click Run.

• You’ll see a message box displaying the range of row numbers. For example, if you select the range of cells B4:C12, the output will show row numbers from 4 to 12.

Sub From_Range()- Provides a name for the sub-procedure of the macro.

Dim Row_First As Long 
Dim Row_Last As Long- Declare the necessary variable for the macro.

 Row_First = Selection.Row- This variable will contain the first row of a collection of rows representing the entire range.

Row_Last = Selection.Row + Selection.Rows.Count - 1 - This variable will contain the last row of a collection of rows representing the entire range.

MsgBox Row_First & " to " & Row_Last - A message is displayed in a dialog box along with a range of row numbers.

End Sub - Ends the sub-procedure of the macro.

Method 3 – Getting Last Row Number from Range

Steps

• Open the VBA window by navigating to the Developer tab and selecting Visual Basic.

• Insert a new module.

• Enter the following code in the module:

Sub last_row()
Dim T1 As Long
Dim w1s As Worksheet
Dim r1ng As Range
Set w1s = Worksheets("Sheet1")
Set r1ng = w1s.Range("B5:C12")
T1 = r1ng.row + r1ng.Rows.Count - 1
MsgBox "Here,Row Number is: " & T1
End Sub

• Save the code.
• Close the Visual Basic window.
• Press Alt+F8 to open the Macro dialog box.
• Select last_row from the list of macros and click Run.
• Close the Visual Basic window.
• Press Alt+F8.
• When the Macro dialogue box opens, select last_row in the Macro name.
• Click on Run.

• You’ll see a message box displaying the last row number (which in this case is 12).

Sub last_row()- Provides a name for the sub-procedure of the macro

Dim T1 As Long
Dim w1s As Worksheet
Dim r1ng As Range - These 3 declare the necessary variable for the macro.

Set w1s = Worksheets("Sheet1") - Specifies the worksheet where the row from the range will be selected.

Set r1ng = w1s.Range("B5:C12") - Specifies the range of the row.

T1 = r1ng.row + r1ng.Rows.Count - 1 - This variable will contain the last row of a collection of rows representing the entire range.

MsgBox "Here,Row Number is: " & T1 - A message is displayed in a dialog box along with a row number.

End Sub - Ends the sub-procedure of the macro.

Method 4 – Obtain Row Number from Active Cell Address

Steps

• Open the VBA window by navigating to the Developer tab and selecting Visual Basic.

• Insert a new module.

• Enter the following code in the module:

Sub GetRowNumber_1()
    rowNumber = Selection.Row
    MsgBox "Row Number: " & rowNumber
End Sub

• Save the code.
• Close the Visual Basic window.
• Press Alt+F8 to open the Macro dialog box.
• Select GetRowNumber_1 from the list of macros and click Run.

• You’ll see a message box displaying the row number of the active cell (Row Number 6).

Sub GetRowNumber_1() - Provides a name for the sub-procedure of the macro

rowNumber = Selection.Row - This variable will contain the number of the row of the range

MsgBox "Row Number: " & rowNumber - A message is displayed in a dialog box along with a row number.

End Sub - Ends the sub-procedure of the macro.

Read More: Excel VBA: Return Row Number of Value

Method 5 – Find Row Number Based on String

Steps

• Open the VBA window by navigating to the Developer tab and selecting Visual Basic.

• Insert a new module.

• Enter the following code in the module:

Sub GetRowNumber_2()
    Dim findName  As Range
Set findName = ActiveSheet.Cells.Find("Stuart")
If Not findName Is Nothing Then
    MsgBox "Row Number: " & findName.Row
Else
    MsgBox "Student not found!"
End If
End Sub

• Save the code.
• Close the Visual Basic window.
• Press Alt+F8 to open the Macro dialog box.
• Select GetRowNumber_2 from the list of macros and click Run.

• You’ll see a message box displaying the row number based on the string Stuart or a message indicating that the student was not found.

Sub GetRowNumber_2()-Provides a name for the sub-procedure of the macro.

Dim findName  As Range- Declares the necessary variable for the macro.

Set findName = ActiveSheet.Cells.Find("Stuart")- Searches for specific information in a range.

If Not findName Is Nothing Then
    MsgBox "Row Number: " & findName.Row
Else
    MsgBox "Student not found!"
End If-In the first line, we inserted the IF statement. If the first statement is true, we will get a message box with a row number; otherwise, we will only get a message box with the message "Students not found."

End Sub- Ends the sub-procedure of the macro.

Read More: Excel VBA: Find String in Column and Return Row Number

Method 6 – Find Row Number Using Input Box

Steps

• Go to the Developer tab on your ribbon.
• Select Visual Basic from the Code group.
• This opens the VBA window where you’ll insert the code.

• Go to the Insert tab in the VBA editor.
• Click on Module from the drop-down.

• As a result, a new module will be created.
• Select the module (if not already selected).
• Enter the following code:

Sub getrownumber_6()
    Dim Student_Name As String
    Dim row1 As Range
    Student_Name = InputBox("What is Name?")
    Set row1 = Cells.Find(What:=Student_Name, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)
    If row1 Is Nothing Then
        MsgBox ("Student Not found")
        Else
        MsgBox (row1.row)
    End If
End Sub

• Save the code.
• Close the Visual Basic window.
• Press Alt+F8.
• In the Macro dialog box, select getrownumber_6 as the Macro name.
• Click Run.

• A message box will appear, prompting you to enter the student’s name.
• Click OK.

• You’ll receive a message box displaying the row number based on the input string.

Sub getrownumber_6()- Provides a name for the sub-procedure of the macro.

 Dim Student_Name As String
 Dim row1 As Range-Declare the necessary variable for the macro.

  Student_Name = InputBox("What is Name?")- Gives the user a dialog box to enter information, and then returns the data they have entered.

Set row1 = Cells.Find(What:=Student_Name, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)- Specify the cell position where the input information from the input box will be selected.

If row1 Is Nothing Then
        MsgBox ("Student Not found")
        Else
        MsgBox (row1.row)
    End If- In the first line of this piece of the code, we inserted the IF statement. A message box with the message "Students not found" will appear if the first statement is true; otherwise, a message box with the row number will appear.

End Sub- Finally, end the sub-procedure of the macro.

Method 7 – Using VBA Split Function

Steps

• To open the VBA window:
  • Go to the Developer tab on your ribbon.
  • Select Visual Basic from the Code group.

• To insert a module:
  • Go to the Insert tab in the VBA editor.
  • Click on Module from the drop-down.

• A new module will be created.
• Select the module (if not already selected).
• Enter the following code:

Sub GetRowNumber_3()
    rowNumber = Split(Selection.Address, "$")(2)
    MsgBox "Row Number: " & rowNumber
End Sub

• Save the code.
• Close the Visual Basic window.
• Press Alt+F8.
• In the Macro dialog box, select GetRowNumber_3 as the Macro name.
• Click Run.

• A message box will appear, displaying the row number based on the selected cell.

Sub GetRowNumber_3()- Provides a name for the sub-procedure of the macro.

rowNumber = Split(Selection.Address, "$")(2)- This variable will contain the number of rows of the range.

MsgBox "Row Number: " & rowNumber- A message is displayed in a dialog box along with a row number.

End Sub- Ends the sub-procedure of the macro.

Method 8 – Get Row Number with Cell Change

Steps

• To open the VBA window:
  • Go to the Developer tab on your ribbon.
  • Select Visual Basic from the Code group.

• When the VBA editor window appears, click on Sheet1 from the Microsoft Excel Objects section.

• Enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim row_numbers As Integer
row_numbers = ActiveCell.Row
        MsgBox "Now, You are in row number " & row_numbers
End Sub

• Save the code.
• Click Run to execute the macro.

• A message box will appear, displaying the row number based on the selected cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range- The Worksheet_SelectionChange event procedure triggers as soon as a cell is chosen.

Dim row_numbers As Integer- Declares the necessary variable for the macro.

row_numbers = ActiveCell.Row- This variable will contain the number of rows of the range.

 MsgBox "Now, You are in row number " & row_numbers- A message is displayed in a dialog box along with a row number.

End Sub- Ends the sub-procedure of the macro.

Method 9 – Find Row Number with If Statement

Steps

• To open the VBA window:
  • Go to the Developer tab on your ribbon.
  • Select Visual Basic from the Code group.

• When the VBA editor window appears, click on Sheet1 from the Microsoft Excel Objects section.

• Enter the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim row_1_number As Integer
row_1_number = ActiveCell.Row
    If ActiveCell.Value <> "" Then
        MsgBox "You are in row number " & row_1_number
    End If
End Sub

• Save the code.
• Click Run to execute the macro.

• A message box will appear, displaying the row number based on the selected cell.

Private Sub Worksheet_SelectionChange(ByVal Target As Range- The Worksheet_SelectionChange event procedure triggers as soon as a cell is chosen.

Dim row_1_number As Integer- Declares the necessary variable for the macro.

row_1_number = ActiveCell.Row- This variable will contain the number of rows in the range.

If ActiveCell.Value <> "" Then
        MsgBox "You are in row number " & row_1_number
    End If- In the first line of this piece of the code, we inserted the If statement. If the first statement is true, we will get a message box with a row number; otherwise, we won't.

End Sub- Ends the sub-procedure of the macro.

Download Practice Workbook

You can download the practice workbook from here: