Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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

If you are looking for special tricks to know “Excel VBA: get row number from range”, you’ve come to the right place. There are numerous ways of “Excel VBA get row number from range”. This article will discuss the details of these methods to get row numbers from a range using VBA code. Let’s follow the complete guide to learn all of this.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article. It contains all the datasets in different spreadsheets for a clear understanding. Try it yourself while you go through the step-by-step process.


9 Suitable Examples to Get Row Number from Range Using VBA in Excel

In the following section, we will use eight effective and tricky examples of “Excel VBA get row number from range”. This section provides extensive details on these methods. You should learn and apply these to improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.


1. Get Row Number from Range

If you want to get row numbers from a range using VBA code in Excel, you need to use the help of VBA. Microsoft Visual Basic for Applications (VBA) is Microsoft’s Event Driven Programming Language. To use this feature you first need to have the Developer tab showing on your ribbon. Click here to see how you can show the Developer tab on your ribbon. Once you have that, follow these detailed steps of “Excel VBA get row number from range”.

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

Get Row Number from Range

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Then, click on Module from the drop-down.

Inserting Module

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then, write down the following code in it.
Sub GetRowNumber()
    rowNumber = Range("B4").row
    MsgBox "Here,Row Number is: " & rowNumber
End Sub
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select GetRowNumber in the Macro name. Click on Run.

Run VBA code

⧭ Output:

  • Therefore, you will get the following message box which shows the row number of cell B4.

🔎 VBA Code Explanation

Sub GetRowNumber()

First of all, provide a name for the sub-procedure of the macro.

rowNumber = Range("B4").row

This variable will contain the number of row of the range.

MsgBox "Here,Row Number is: " & rowNumber

In this message box, a message is displayed in a dialog box along with a row number.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Find Row Number Using VBA in Excel (4 Macros)


2. Get Row Number from a Selection

Now, we will show another VBA code to get row numbers from a range using the VBA code in Excel. If you want to get row numbers from a range, you need to use the following VBA code. Let’s walk through the following steps to get row numbers from a range using VBA code in Excel.

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

Get Row Number from a Selection

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a .bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

Insert Module

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
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
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select From_Range in the Macro name. Click on Run.

⧭ Output:

  • Following is the range of row numbers you will get. Here, we select the range of the cells B4:C12. That’s why the output shows the row numbers from 4 to 12.

Show Output

🔎 VBA Code Explanation

Sub From_Range()

First of all, provide a name for the sub-procedure of the macro.

Dim Row_First As Long
Dim Row_Last As Long

Next, 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

In this message box, a message is displayed in a dialog box along with a range of row numbers.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Get Row Number from Cell Value in Excel (5 Methods)


3. Getting Last Row Number from Range

Previously you have seen we use VBA code to find the row numbers of a range, now, we will show another VBA code to get row numbers from a range using VBA code in Excel. You have to follow these detailed steps to get the last row number from the range using VBA code in Excel.

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

Get Last Row Number from Range

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
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
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select last_row in the Macro name. Click on Run.

Run VBA code

⧭ Output:

  • Therefore, we get 12 as the last row number.

show output

🔎 VBA Code Explanation

Sub last_row()

First of all, provide a name for the sub-procedure of the macro

Dim T1 As Long
Dim w1s As Worksheet
Dim r1ng As Range

Next, declare the necessary variable for the macro.

Set w1s = Worksheets("Sheet1")

After that, specify the worksheet where the row from the range will be selected.

Set r1ng = w1s.Range("B5:C12")

Later, specify 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

In this message box, a message is displayed in a dialog box along with a row number.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Use Excel Formula to Find Last Row Number with Data (2 Ways)


4. Obtain Row Number from Active Cell Address

Now, we will show another VBA code to obtain the row numbers from the active cell address using the VBA code in Excel. Let’s walk through the following steps to obtain the row numbers from the active cell address using the VBA code.

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

get Row Number from range in Active Cell Address

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
Sub GetRowNumber_1()
    rowNumber = Selection.Row
    MsgBox "Row Number: " & rowNumber
End Sub
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select GetRowNumber_1 in the Macro name. Click on Run.

⧭ Output:

  • Therefore, you will get the following message box which shows the row number of the active cells.

get the row number as 6 from range

🔎 VBA Code Explanation

Sub GetRowNumber_1()

First of all, provide 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

In this message box, a message is displayed in a dialog box along with a row number.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Use Variable Row Number as Cell Reference in Excel


5. Find Row Number Based on String

In the previous method, we use VBA code to find row numbers based on active cells, now, we will show another VBA code to find row numbers based on strings using VBA code in Excel. You have to follow these detailed steps to find row numbers based on strings using VBA code in Excel.

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

get Row Number from range Based on String

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

insert module

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
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
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select GetRowNumber_2 in the Macro name. Click on Run.

⧭ Output:

  • Therefore, you will get the following message box which shows the row number based on the string.

show output

🔎 VBA Code Explanation

Sub GetRowNumber_2()

First of all, provide a name for the sub-procedure of the macro.

Dim findName  As Range

Next, declare the necessary variable for the macro.

Set findName = ActiveSheet.Cells.Find("Stuart")

In a later step, search 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 of this piece of the code, we inserted the IF statement. In this 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

Finally, end the sub-procedure of the macro.

Read More: Find String in Column and Return Row Number in Excel (7 Ways)


6. Find Row Number Using Input Box

Now, we will use the Input Box function which is useful for obtaining a single input from the user. We will show another VBA code to find row numbers using the input box by applying the VBA code in Excel. Let’s walk through the following steps to find row numbers using the input box by applying the VBA code.

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

get Row Number from range Using Input Box

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

insert module

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
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
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select getrownumber_6 in the Macro name. Click on Run.

  • Therefore, a message box will appear where you have to name of the student.
  • Click on OK.

inserting student's name in the input box

⧭ Output:

  • Therefore, you will get the following message box which shows the row number based on the string.

get the row number as 8 from range

🔎 VBA Code Explanation

Sub getrownumber_6()

First of all, provide a name for the sub-procedure of the macro.

 Dim Student_Name As String
 Dim row1 As Range

Next, declare the necessary variable for the macro.

  Student_Name = InputBox("What is Name?")

Gives the user a dialog box to enter information, 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)

After that, 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.


7. Using VBA Split Function

In this method, we will show another VBA code to get row numbers from a range using VBA split function in Excel. You have to follow these detailed steps to get row numbers from a range using the VBA split function in Excel.

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

Using VBA Split Function to get row number from range

⧭ Insert Module:

  • VBA modules hold the code in the Visual Basic Editor. It has a.bcf file extension. We can create or edit one easily through the VBA editor window. To insert a module for the code, go to the Insert tab on the VBA editor. Afterward, click on Module from the drop-down.

insert module

  • As a result, a new module will be created.

⧭ Insert VBA Code:

  • Now select the module if it isn’t already selected. Then write down the following code in it.
Sub GetRowNumber_3()
    rowNumber = Split(Selection.Address, "$")(2)
    MsgBox "Row Number: " & rowNumber
End Sub
  • Next, save the code.

⧭ Run VBA Code:

  • Afterward, close the Visual Basic window. After that press Alt+F8.
  • When the Macro dialogue box opens, select GetRowNumber_3 in the Macro name. Click on Run.

⧭ Output:

  • Therefore, you will get the following message box which shows the row number based on the selected cell.

show the output

🔎 VBA Code Explanation

Sub GetRowNumber_3()

First of all, provide 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

In this message box, a message is displayed in a dialog box along with a row number.

End Sub

Finally, end the sub-procedure of the macro.

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


8. Get Row Number with Cell Change

Previously, you have seen we use the Split function to find row numbers, now, we will show another VBA code to get row numbers with cell change using VBA code in Excel. Lets’s walk through the following steps to get row numbers with cell change using VBA code in Excel.

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Afterward, select Visual Basic from the Code group.

Get Row Number from range with Cell Change

⧭ Select Worksheet:

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

select Sheet1 from the Microsoft Excel Objects section

⧭ Insert VBA Code:

  • Then write down the following code in it.
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
  • Next, save the code.

⧭ Run VBA Code:

  • Lastly, you have to click on Run to run the macro.

 

⧭ Output:

  • Therefore, you will get the following message box which shows the row number based on the selected cell.

show the row number of the selected cell

🔎 VBA Code Explanation

Private Sub Worksheet_SelectionChange(ByVal Target As Range

Here, the Worksheet_SelectionChange event procedure triggers as soon as a cell is chosen.

Dim row_numbers As Integer

Next, declare 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

In this message box, a message is displayed in a dialog box along with a row number.

End Sub

Finally, end the sub-procedure of the macro.

Read More: How to Get Row Number of Current Cell in Excel (4 Quick Ways)


9. Find Row Number with If Statement

In this last method, we will show another effective VBA code to find row numbers with IF conditions by applying the VBA code in Excel. You have to follow these detailed steps to get row numbers with cell change using VBA code in Excel.

📌 Steps:

⧭ Open VBA Window:

  • VBA has its own separate window to work with. You have to insert the code in this window too. To open the VBA window, go to the Developer tab on your ribbon. Then select Visual Basic from the Code group.

get Row Number from range using IF Condition

⧭ Select Worksheet:

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

Select Sheet1 in the VBA Editor

⧭ Insert VBA Code:

  • Then write down the following code in it.
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
  • Next, save the code.

⧭ Run VBA Code:

  • Lastly, you have to click on Run to run the macro.

⧭ Output:

  • Therefore, you will get the following message box which shows the row number based on the selected cell.

get the row number from range

🔎 VBA Code Explanation

Private Sub Worksheet_SelectionChange(ByVal Target As Range

Here, the Worksheet_SelectionChange event procedure triggers as soon as a cell is chosen.

Dim row_1_number As Integer

Next, declare 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

Finally, end the sub-procedure of the macro.

Read More: [Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)


Conclusion

That’s the end of today’s session. I strongly believe that from now, you may learn “Excel VBA get row number from range”. If you have any queries or recommendations, please share them in the comments section below.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions. Keep learning new methods and keep growing!


Related Articles

Saquib

Saquib

Welcome to my Profile. I am working on and researching Microsoft Excel right now, and I will be posting articles about it here. I received a B.Sc. in Naval Architecture and Marine Engineering from the Bangladesh University of Engineering and Technology (BUET). Having studied naval architecture, I have a strong interest in research and development. Always try to learn from different sources and come up with creative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo