How to Find a String in a Column and Return a Row Number Using VBA (6 Methods)

Method 1 – Returning Row Number Based on a String

• Open the VBA Window:
  • Go to the Developer tab on the Excel ribbon.
  • Select Visual Basic from the Code group.
  • This opens the VBA editor window.

• Insert a Module:
  • In the VBA editor, go to the Insert tab.
  • Click on Module from the dropdown.
  • A new module will be created.

• Enter VBA Code:
  • If the module isn’t already selected, click on it.
  • Add the following code:

Sub GetRowNumber_2()
Dim findName  As Range
Set findName = ActiveSheet.Cells.Find("Stuart")
If Not findName Is Nothing Then
MsgBox "Row Number: " & findName.Row
Else
MsgBox "Student not found!"
End If
End Sub

• • Save the code.

• Run the VBA Code:
  • Close the Visual Basic window.
  • Press Alt+F8 to open the Macro dialog box.
  • Select GetRowNumber_2 from the list of macros.
  • Click Run.

• Output:
  • You’ll see a message box displaying the row number based on the specified string.

Sub GetRowNumber_2()

Dim findName  As Range

Set findName = ActiveSheet.Cells.Find("Stuart")

If Not findName Is Nothing Then
MsgBox "Row Number: " & findName.Row
Else
MsgBox "Student not found!"
End If

End Sub

Read More: Excel VBA: Return Row Number of Value

Method 2 – Using Input Box

• Open the VBA Window:
  • Go to the Developer tab on your Excel ribbon.
  • Select Visual Basic from the Code group.
  • This opens the Visual Basic for Applications (VBA) editor window.

• Insert a Module:
  • In the VBA editor, go to the Insert tab.
  • Click on Module from the dropdown.
  • A new module will be created.

• Enter VBA Code:
  • If the module isn’t already selected, click on it.
  • Add the following code:

Sub getrownumber_6()
Dim Student_Name As String
Dim row1 As Range
Student_Name = InputBox("What is Name?")
Set row1 = Cells.Find(What:=Student_Name, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False)
If row1 Is Nothing Then
MsgBox ("Student Not found")
Else
MsgBox (row1.row)
End If
End Sub

• • Save the code.

• Run the VBA Code:
  • Close the Visual Basic window.
  • Press Alt+F8 to open the Macro dialog box.
  • Select GetRowNumber_6 from the list of macros.
  • Click Run.

• Input Prompt:
  • A message box will appear, prompting you to enter the name of the student.
  • Click OK.

• Output:
  • You’ll receive a message box displaying the row number based on the entered student name.

Sub getrownumber_6()

Dim Student_Name As String
Dim row1 As Range

Student_Name = InputBox("What is Name?")

Set row1 = Cells.Find(What:=Student_Name, LookIn:=xlFormulas, LookAt _

:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _

False, SearchFormat:=False)

If row1 Is Nothing Then
MsgBox ("Student Not found")
Else
MsgBox (row1.row)
End If

End Sub

Read More: How to Find Row Number Using VBA in Excel 

Method 3 – Use the Range.Find Method

• Open the VBA Window:
  • Go to the Developer tab on your Excel ribbon.
  • Select Visual Basic from the Code group.
  • This opens the Visual Basic for Applications (VBA) editor window.

• Insert a Module:
  • In the VBA editor, go to the Insert tab.
  • Click on Module from the dropdown.
  • A new module will be created.

• Enter VBA Code:
  • If the module isn’t already selected, click on it.
  • Add the following code:

Sub Return_Row()
Dim W1S As Worksheet
Dim Row_Match As Long
Dim Value_Search As String
Set W1S = Worksheets("Sheet1")
Value_Search = "Peter"
Row_Match = W1S.Cells.find(What:=Value_Search, After:=Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row
MsgBox "Here,Row Number is: " & Row_Match
End Sub

• • Save the code.

• Run the VBA Code:
  • Close the Visual Basic window.
  • Press Alt+F8 to open the Macro dialog box.
  • Select Return_Row from the list of macros.
  • Click Run.

• Output:
  • You’ll receive a message box displaying the row number based on the specified string (Peter in this example).

Sub Return_Row()

Dim W1S As Worksheet
Dim Row_Match As Long
Dim Value_Search As String

Set W1S = Worksheets("Sheet1")

Value_Search = "Peter"

Row_Match = W1S.Cells.find(What:=Value_Search, After:=Cells(1, 1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Row

MsgBox "Here,Row Number is: " & Row_Match

End Sub

Read More: How to Get Row Number from Range with Excel VBA

Method 4 – Using Columns Property

• Open the VBA Window:
  • The VBA (Visual Basic for Applications) window is where we’ll insert our code. To access it:
    • Go to the Developer tab on your ribbon.
    • Select Visual Basic from the “Code” group.

• Insert a Module:
  • VBA modules store code in the Visual Basic Editor.
  • To insert a module:
    • Go to the Insert tab in the VBA editor.
    • Click on Module from the drop-down menu.
    • A new module will be created.

• Insert VBA Code:
  • If the module isn’t already selected, choose it.
  • Enter the following code within the module:

Sub Return_Row_1()
Dim Row_1 As Long
Row_1 = Columns(3).Find(What:="John").Row
MsgBox "Here,Row Number is: " & Row_1
End Sub

• Save the code.
• Run the VBA Code:
  • Close the Visual Basic window.
  • Press Alt+F8.
  • In the Macro dialog box, select Return_Row_1 as the macro name.
  • Click Run.

Output:

• • You’ll see a message box displaying the row number corresponding to the specified string (John).

Sub Return_Row_1()

Dim Row_1 As Long

Row_1 = Columns(3).Find(What:="John").Row

MsgBox "Here,Row Number is: " & Row_1

End Sub

Method 5 – Utilizing VBA For Loop

• Open the VBA Window:
  • The VBA (Visual Basic for Applications) window is where we’ll insert our code. To access it:
    • Go to the Developer tab on your ribbon.
    • Select Visual Basic from the “Code” group.

• Insert a Module:
  • VBA modules store code in the Visual Basic Editor.
  • To insert a module:
    • Go to the Insert tab in the VBA editor.
    • Click on Module from the drop-down menu.
    • A new module will be created.

• Insert VBA Code:
  • If the module isn’t already selected, choose it.
  • Enter the following code within the module:

Sub Return_Row_2()
Dim ws1 As Worksheet
Dim Row_Match As Long
Dim k As Long
Dim Value_Search As String
Set ws1 = Worksheets("Sheet1")
Value_Search = "John"
For k = 1 To 100
If StrComp(ws1.Range("C" & k).Value, Value_Search, vbTextCompare) = 0 Then
Row_Match = k
Exit For
End If
Next k
MsgBox "Here,Row Number is: " & Row_Match
End Sub

• • Save the code.

• Run VBA Code:
  • Close the Visual Basic window.
  • Press Alt+F8.
  • In the Macro dialog box, select Return_Row_2 as the macro name.
  • Click Run.

• Output:
  • You’ll see a message box displaying the row number corresponding to the specified string (John).

Sub Return_Row_2()

Dim ws1 As Worksheet
Dim Row_Match As Long
Dim k As Long
Dim Value_Search As String

Set ws1 = Worksheets("Sheet1")

Value_Search = "John"

For k = 1 To 100
If StrComp(ws1.Range("C" & k).Value, Value_Search, vbTextCompare) = 0 Then
Row_Match = k
Exit For
End If
Next k

MsgBox "Here,Row Number is: " & Row_Match

End Sub

Method 6 – Applying VBA StrComp Function

• Open the VBA Window:
  • VBA has its own separate window for working with code.
  • To open the VBA window:
    • Go to the Developer tab on your ribbon.
    • Select Visual Basic from the Code group.

• Insert a Module:
  • VBA modules store code in the Visual Basic Editor.
  • To insert a module:
    • Go to the Insert tab in the VBA editor.
    • Click on Module from the drop-down menu.
    • A new module will be created.

• Insert VBA Code:
  • If the module isn’t already selected, choose it.
  • Enter the following code within the module:

Sub Return_R0W_Array()

Dim Ws1 As Worksheet
Dim Row_match As Long
Dim k As Long
Dim Value_Search As String
Dim Data_array As Variant
Set Ws1 = Worksheets("Sheet1")
Data_array = Ws1.Range("A1:E100")
Value_Search = "John"
For k = 1 To 100
If StrComp(Data_array(k, 3), Value_Search, vbTextCompare) = 0 Then
Row_match = k
Exit For
End If
Next k
MsgBox "Here,Row Number is: " & Row_match
End Sub

• • Save the code.

• Run VBA Code:
  • Close the Visual Basic window.
  • Press Alt+F8.
  • In the Macro dialog box, select Return_R0W_Array as the macro name.
  • Click Run.

• Output:
  • You’ll see a message box displaying the row number corresponding to the specified string (John).

Sub Return_R0W_Array()

Dim Ws1 As Worksheet
Dim Row_match As Long
Dim k As Long
Dim Value_Search As String
Dim Data_array As Variant

Set Ws1 = Worksheets("Sheet1")

Data_array = Ws1.Range("A1:E100")

Value_Search = "John"

For k = 1 To 100
If StrComp(Data_array(k, 3), Value_Search, vbTextCompare) = 0 Then
Row_match = k
Exit For
End If
Next k

MsgBox "Here,Row Number is: " & Row_Match

End Sub

Download Practice Workbook

You can download the practice workbook from here: