Using Excel VBA to Return the Row Number of Value

Method 1 – Using the Input Box

Enable the Developer tab on the ribbon. Click: How to Show the Developer Tab on the Ribbon.

Steps

Go to the Developer tab.
Select Visual Basic in Code.

In the Visual Basic window, select Insert.
Choose Module.

Enter the following code in the Module.

Sub GetRowNum1()
    Dim Marks As String
    Dim rowX As Range
    Marks = InputBox("What is the value?")
    Set rowX = Cells.Find(What:=Marks, LookIn:=xlFormulas, LookAt _
   :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
    If rowX Is Nothing Then
        MsgBox ("Value Not found")
        Else
        MsgBox ("Row Number is: " & rowX.Row)
    End If
End Sub

Close the Visual Basic window.
Go to the Developer tab and select Macros in Code.

In the Macro dialog box, select GetRowNum1 in Macro name.
Click Run.

An input box will be displayed. Add values.
Click OK.

The row number is returned.

Code Breakdown

Sub GetRowNum1()

names the sub-procedure.

 Dim Marks As String
 Dim rowX As Range

declares the necessary variable.

  Marks = InputBox("What is Value?")

displays the dialog box to enter information and returns the entered data.

Set rowX = Cells.Find(What:=Marks, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

specifies the cell position of the input information.

If rowX Is Nothing Then
 MsgBox ("Value Not found")
 Else
 MsgBox ("Row Number is: " & rowX.Row)
 End If
End Sub

With the If statement, a message box with the message “Value not found” will appear if the first statement is true. Otherwise, a message box with the row number will be displayed.

End Sub

ends the sub-procedure.

Method 2 – Utilizing a VBA If Statement

Steps

Go to the Developer tab.
Select Visual Basic in Code.

In the Visual Basic window, select Insert.
Choose Module.

Enter the following code in the Module.

Sub GetRowNum2()
Dim MyVal As Integer
Dim LastRow As Long
Dim RowNoList As String
MyVal = 84
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
For Each cel In Range("C2:C" & LastRow)
 If cel.Value = MyVal Then
 If RowNoList = "" Then
 RowNoList = RowNoList & cel.Row
 Else
 RowNoList = RowNoList & ", " & cel.Row
 End If
 End If
Next cel
MsgBox "Row Number is: " & RowNoList
End Sub

Close the Visual Basic window.
Go to the Developer tab and select Macros in Code.

In the Macro dialog box, select GetRowNum2 in Macro name.
Click Run.

The row number is displayed.

Code Breakdown

Sub GetRowNum2()

names the sub-procedure.

Dim MyVal As Integer
Dim LastRow As Long
Dim RowNoList As String

declares the necessary variable.

MyVal = 84

defines the value to get the row number.

LastRow = Cells(Rows.Count, "C").End(xlUp).Row

defines the last row of the column containing the value.

For Each cel In Range("C2:C" & LastRow)
If cel.Value = MyVal Then
If RowNoList = "" Then
RowNoList = RowNoList & cel.Row
Else
RowNoList = RowNoList & ", " & cel.Row
End If
End If
Next cel

the For statement with the If condition determines that: if the cell value is equal to the given value, it will return the row number. Otherwise, it will return a blank. It moves to the next cell and performs the same loop.

MsgBox "Row Number is: " & RowNoList

a message is displayed in a dialog box along with a row number.

End Sub

ends the sub-procedure.

Method 3 – Using the VBA Find Method

Steps

Go to the Developer tab.
Select Visual Basic in Code.

In the Visual Basic window, select Insert.
Choose Module.

Enter the following code in the Module.

Sub GetRowNum3()
Dim SearchRang As Range
Dim FRow As Range
Set SearchRang = Range("C1", Range("C65536").End(xlUp))
Set FRow = SearchRang.Find(65, LookIn:=xlValues, LookAt:=xlWhole)
MsgBox "Row Number is: " & FRow.Row
End Sub

Close the Visual Basic window.
Go to the Developer tab and select Macros in Code.

In the Macro dialog box, select GetRowNum3 in Macro name.
Click Run.

The row number is displayed.

Code Breakdown

Sub GetRowNum3()

names the sub-procedure.

Dim SearchRang As Range
Dim FRow As Range

declares the necessary variable.

Set SearchRang = Range("C1", Range("C65536").End(xlUp))

sets the search range of the given value.

Set FRow = SearchRang.Find(65, LookIn:=xlValues, LookAt:=xlWhole)

searches the row of the assigned value.

MsgBox "Row Number is: " & FRow.Row

a message is displayed in a dialog box with a row number.

End Sub

ends the sub-procedure.

Method 4 – Using the Columns Property

Steps

Go to the Developer tab.
Select Visual Basic in Code.

In the Visual Basic window, select Insert.
Choose Module.

Enter the following code in the Module.

Sub GetRowNum4()
Dim Row_1 As Long
Row_1 = Columns(3).Find(What:=26).Row
MsgBox "Row Number is: " & Row_1
End Sub

Close the Visual Basic window.
Go to the Developer tab and select Macros in Code.

In the Macro dialog box, select GetRowNum4 in Macro name.
Click Run.

The row number is displayed.

Code Breakdown

Sub GetRowNum4()

names the sub-procedure.

Dim Row_1 As Long

declares the necessary variable.

 Row_1 = Columns(3).Find(What:=26).Row

contains the row number of the range.

 MsgBox "Row Number is: " & Row_1

a message is displayed in a dialog box with a row number.

End Sub

ends the sub-procedure.

Method 5 – Applying the VBA StrComp Function

Steps

Go to the Developer tab.
Select Visual Basic in Code.

In the Visual Basic window, select Insert.
Choose Module.

Enter the following code in the Module.

Sub GetRowNum5()
Dim WS1 As Worksheet
Dim Row_match As Long
Dim J As Long
Dim Value_Search As String
Dim Data_array As Variant
Set WS1 = Worksheets("Applying VBA StrComp Function")
Data_array = WS1.Range("A1:E100")
Value_Search = 56
For J = 1 To 100
If StrComp(Data_array(J, 3), Value_Search, vbTextCompare) = 0 Then
 Row_match = J
 Exit For
 End If
Next J
MsgBox "Row Number is: " & Row_match
End Sub

Close the Visual Basic window.
Go to the Developer tab and select Macros in Code.

In the Macro dialog box, select GetRowNum5 in Macro name.
Click Run.

The row number is displayed.

Code Breakdown

Sub GetRowNum5()

names the sub-procedure.

Dim WS1 As Worksheet
Dim Row_match As Long
Dim J As Long
Dim Value_Search As String
Dim Data_array As Variant

declares the necessary variable.

Set WS1 = Worksheets("Applying VBA StrComp Function")

specifies the worksheet name.

For J = 1 To 100

If StrComp(Data_array(J, 3), Value_Search, vbTextCompare) = 0 Then
 Row_match = J
 Exit For
 End If
Next J

the For statement starts looping from the declared column. If the iteration variable finds the value 56 in any row of the declared column, it returns the row number.

MsgBox "Row Number is: " & Row_match

a message is displayed in a dialog box with a row number.

End Sub

Using Excel VBA to Return the Row Number of Value – 5 Methods

Method 1 – Using the Input Box

Method 2 – Utilizing a VBA If Statement

Method 3 – Using the VBA Find Method

Method 4 – Using the Columns Property

Method 5 – Applying the VBA StrComp Function

Leave a reply Cancel reply