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

Macro 1 – VBA to Find Row Number by Changing Selection

Steps:

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

VBA to Find Row Number by Changing Selection

Add the following code –

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

Do not run the codes, go back to your sheet.

Code Breakdown:

We created a Private Sub procedure – Worksheet_SelectionChange.
Declared a variable Rnumber as an Integer.
Row will determine the row number of the active cell.
The If statement will check the active cell whether it is empty or not, and MsgBox will show the output.

Click on any used cell and it will show you the row number.

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

Steps:

Press ALT + F11 to open the VBA window.

Click as follows to insert a new module: Insert > Module.

Add the following code 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

Go back to your sheet.

Code Breakdown:

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

Select a cell and click as follows: Developer > Macros.

Select the macro name and press Run.

The row number of the selected cell will be displayed in output cell.

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

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

Steps:

Follow the first two steps from the previous method to insert a new module.
Insert the following code 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

Go back to your sheet.

Code Breakdown:

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.
Range will search the value through the mentioned column.
The If and Else statement will show the result using MsgBox.

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

A notification box will show you the row number.

Macro 4 – Button to Find Row Number

Steps:

Insert a new module.
Enter the following code 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