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

Get FREE Advanced Excel Exercises with Solutions!

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.

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 I declared a variable Rnumber as an 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.


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

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 procedure.
  • And wBook and wSheet are declared as Worksheet and fCell is declared as Range.
  • The wBook and wSheet are set 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: 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:

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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo