Home » Macros & Excel VBA » How to Get Row Number from Range with Excel VBA (9 Methods)

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

Avatar photo
Written by Saquib Ahmad Shuvo
Last updated: Jul 25, 2024

Method 1 – Get Row Number from Range

Steps

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

Get Row Number from Range

  • Insert a new module.

Inserting 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.

Run VBA code

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

VBA Code Explanation

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.

Get Row Number from a Selection

  • Insert a new module.

Insert 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.

Show Output

VBA Code Explanation

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.

Get Last Row Number from Range

  • 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.

Run VBA code

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

show output

VBA Code Explanation

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.

get Row Number from range in Active Cell Address

  • 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).

get the row number as 6 from range

VBA Code Explanation

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.

get Row Number from range Based on String

  • Insert a new module.

insert 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.

show output

VBA Code Explanation

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.

get Row Number from range Using Input Box

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

insert module

  • 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.

inserting student's name in the input box

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

get the row number as 8 from range

VBA Code Explanation

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.

Using VBA Split Function to get row number from range

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

insert module

  • 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.

show the output

VBA Code Explanation

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.

Get Row Number from range with Cell Change

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

select 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.

show the row number of the selected cell

VBA Code Explanation

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.

get Row Number from range using IF Condition

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

Select Sheet1 in the VBA Editor

  • 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.

get the row number from range

VBA Code Explanation

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:

 

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Saquib Ahmad Shuvo
Saquib Ahmad Shuvo

Saquib Ahmad Bhuiyan, holding a BSc degree in Naval Architecture & Marine Engineering from Bangladesh University of Engineering and Technology, skillfully integrates engineering expertise with a passion for Excel. He provides solutions to complex issues as an Excel & VBA Content Developer for ExcelDemy Forum. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing his commitment to excellence. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, Rhinoceros,... Read Full Bio

3 Comments
  1. Reply
    Haresh Beladia Jun 14, 2023 at 9:50 PM

    First I would like to go to A51 row number and then copy data from A51 to L126

  2. Reply
    Haresh Beladia Jun 14, 2023 at 9:58 PM

    If I want to go to A51 row first and then copy all data from A51 to L126. How can I code the same?

    • Reply Avatar photo
      Md. Abdur Rahim Rasel Jun 21, 2023 at 12:25 PM

      Greetings Haresh Beladia,
      Thanks for sharing your problem with us!
      You may use the below VBA code to fix your problem.

       Sub Copy_Data()
	' Define the source and destination ranges
	Dim SourceRange As Range
	Dim DestinationRange As Range
	Set SourceRange = Worksheets("Source").Range("A51:L126")
	Set DestinationRange = Worksheets("Destination").Range("A51:L126")
	' Copy the values from the source range to the destination range
	DestinationRange.Value = SourceRange.Value
End Sub

      You have copied all the data from cell A51 to cell L126 using the VBA code above.
      Please download the Excel file for solving your problem and practice with it.
      https://www.exceldemy.com/wp-content/uploads/2023/06/Copy-Data.xlsm
      If you have any more questions, please let us know in the comments.
      Regards
      Md. Abdur Rahim Rasel (Exceldemy Team)

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

See Our Reviews at

trustpilot review

ExcelDemy Consulting Services

Submit Excel Task

ExcelDemy Online Training


Join ExcelDemy Forum
DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo