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.
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.
- 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.
- 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.
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.
- 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.
- 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.
- 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.
- How to Increment Row Number in Excel Formula (6 Handy Ways)
- Get Row Number from Range with Excel VBA (9 Examples)
- How to Return Row Number of a Cell Match in Excel (7 Methods)
- How to Get Row Number from Cell Value in Excel (5 Methods)
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.
- 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.
- 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.
- 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.
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.
- 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.
- 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.
- 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, 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.
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.