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

We can find row numbers in Excel using many ways but VBA offers more features and customizations. By which we can find row numbers in smart ways. Today this article is going to show 4 useful macros to find row number in Excel using VBA.

How to Find Row Number Using VBA in Excel: 4 Macros

Get introduced to our dataset that we’ll use to explore the methods that represent some salesperson sales in different regions.

Macro 1: VBA to Find Row Number by Changing Selection

First, we’ll use a macro in Excel VBA to find the row number by selecting any cell. This means if you just select any used cell, the macro will show the row number instantly. For that, you will have to keep the codes in a sheet, not in the module.

Steps:

• Right-click on the sheet title and select View Code from the context menu.

• Then write the following codes-

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rnumber As Integer
Rnumber = ActiveCell.row
If ActiveCell.Value <> "" Then
MsgBox "The row number of the clicked cell is: " & Rnumber
End If
End Sub

• Later, no need to run the codes, just go back to your sheet.

• Now just click on any used cell and it will show you the row number.

Macro 2: Find Row Number of an Active Cell Using VBA

This macro will return the row number of an active cell in a specified cell of our sheet. So, we’ll have to mention the worksheet name and output cell in our codes. Here, we’ll use Cell D14 as our output cell.

Steps:

• Press ALT + F11 to open the VBA window.

• Next, click as follows to insert a new module: Insert > Module.

• After that, type the following codes in the module-

Sub Find_Row_Number_of_an_Active_Cell()
Dim wSheet As Worksheet
Set wSheet = Worksheets("Active Cell")
wSheet.Range("D14") = ActiveCell.row
End Sub

• Then turn back to your sheet.

• Now select a cell and click as follows: Developer > Macros.

• After appearing in the Macro dialog box, just select the macro name and press Run.

Soon after, you will see that the row number of the selected cell is returned in our output cell.

You can see that the B8 cell has been selected, so 8 is the output.

Read More: How to Get Row Number from Range with Excel VBA

Macro 3: VBA to Find Row Number by Matching a Value

If you want to find the row number by searching for a value then this macro is for you. You will have to mention the search value and column number in the codes as shown in the image below.

Steps:

• Follow the first two steps from the previous method to insert a new module.
• Then, insert the following codes in it-

Sub Find_Row_Matching_a_Value()
Dim wBook As Workbook
Dim wSheet As Worksheet
Dim fCell As Range
Set wBook = ActiveWorkbook
Set wSheet = ActiveSheet
Const Matching_Value As String = "Luka"
Set fCell = wSheet.Range("B:B").Find(What:=Matching_Value)
If Not fCell Is Nothing Then
MsgBox (Matching_Value & " is located in row: " & fCell.row)
Else
MsgBox (Matching_Value & " Not matched")
End If
End Sub

• After that, go back to your sheet.

• Later, follow the 5th step from the previous method to open the Macro dialog box.
• Select the macro name and just press Run.

Soon a notification box will show you the row number.

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

Macro 4: Button to Find Row Number

In our last method, we’ll show you the smartest method to determine a row number using VBA macros. We’ll make a button and will assign a macro with it. When we click on the button, it will open an input box where we can give input the searching value for which we want the row number. The previous macro could search through a mentioned column but this macro can search for any column, anywhere in the sheet.

Steps:

• Again follow the first two steps from the second method to insert a new module.
• Next, insert the following codes in it-

Sub Find_Row_Number()
Dim mValue As String
Dim mrrow As Range
mValue = InputBox("Insert a value")
Set mrrow = Cells.Find(What:=mValue, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If mrrow Is Nothing Then
MsgBox ("No Match")
Else
MsgBox (mrrow.row)
End If
End Sub

• Then go back to your sheet.

• Later, click Developer > Insert and then select the Button command from the Form Controls section.

• Then you will get a plus sign with your cursor, drag anywhere by clicking on your sheet according to your desired size, and then release the click.

• After releasing the mouse the Assign Macro dialog box will open up automatically.
• Select the macro name as mentioned in the codes.
• Then just press OK.

• Next, right-click on the button and select Edit Text to edit the button name.

• Type the button name, then click anywhere outside the button and the name will be changed.

• Now click on the button, and it will open an input box.
• Finally, just insert the search value and press OK.

Now take a look, it’s showing the row number of the matched value.

Read More: Excel VBA: Return Row Number of Value

Download Practice Workbook

You can download the free Excel workbook from here and practice independently.

Conclusion

That’s all for the article. I hope the procedures described above will be good enough to find row number in Excel using VBA. Feel free to ask any question in the comment section and please give me feedback.