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.


Download Practice Workbook

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


4 Macros to Find Row Number Using VBA in Excel

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

Find Row Number in Excel VBA


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.

VBA to Find Row Number by Changing Selection

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

VBA to Find Row Number by Changing Selection

Code Breakdown:

  • First, I created a Private Sub procedure – Worksheet_SelectionChange.
  • Then declared a variable Rnumber as Integer.
  • row will determine the row number of the active cell.
  • Next, the If statement will check the active cell whether it is empty or not, and then MsgBox will show the output.

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

Read More: Excel VBA: Find String in Column and Return 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.

Find Row Number of an Active Cell Using VBA

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

Find Row Number of an Active Cell Using VBA

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

Find Row Number of an Active Cell Using VBA

Code Breakdown:

  • Here, Find_Row_Number_of_an_Active_Cell() is the Sub
  • The wSheet is declared as a Worksheet
  • Then the Set statement will select the active cell
  • Range will return the row number in the output cell.

  • 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 of Current Cell in Excel (4 Quick Ways)


Similar Readings


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:

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.

VBA to Find Row Number by Matching a Value

Code Breakdown:

  • Here, the Find_Row_Matching_a_Value() is the Sub
  • And wBook and wSheet are declared as Worksheet and fCell is declared as Range.
  • The wBook and wSheet are setteed for ActiveWorkbook and ActiveSheet.
  • Const will take input for the searching value.
  • Later, Range will search the value through the mentioned column.
  • Next, the If and Else statement will show the result using MsgBox.

VBA to Find Row Number by Matching a Value

Soon a notification box will show you the row number.

Read More: Excel VBA: Return Row Number of Value (5 Suitable Methods)


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’ll 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:

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.

Code Breakdown:

  • First, I created a Sub procedure Find_Row_Number().
  • Then declared two variables, mValue as String and row as Range.
  • Then used the InputBox to insert a value.
  • Later, the Set and If statement will find the row number if it is not empty.
  • Finally, the MsgBox will show the output.

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

Button to Find Row Number

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

Button to Find Row Number

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

Button to Find Row Number

  • 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, 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: Find String in Column and Return Row Number in Excel (7 Ways)


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. Visit ExcelDemy to explore more.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo