Home » Macros & Excel VBA » Using Excel VBA to Return the Row Number of Value – 5 Methods

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

Avatar photo
Written by Durjoy Paul
Last updated: Jun 5, 2024

 

 

Method 1 – Using the Input Box

Enter the VBA code using the input box and show the output.

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.

Using VBA Input Box to Return Row Number of Value

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

  • The row number is returned.

Return Row Number of value Utilizing VBA Input Box

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.

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

Method 2 – Utilizing a VBA If Statement

Use the 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.

Utilizing VBA If Statement to Return Row Number of Value

  • The row number is displayed.

Return Row Number of Value Using VBA If Statement

 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

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

Use of VBA Find Method to Return Row Number of Value

  • The row number is displayed.

Return Row Number of Value Using VBA Find Method in excel

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.

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

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.

Using Columns Property to Return Row Number of Value

  • The row number is displayed.

Return Row Number of Value Utilizing Columns Property

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.

Read More: Excel VBA: Find String in Column and Return Row Number

Method 5 – Applying the VBA StrComp Function

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

Applying VBA StrComp Function to Return Row Number of Value

  • The row number is displayed.

Return Row Number of Value Utilizing VBA StrComp Function

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

ends the sub-procedure.

Download Practice Workbook

Download the practice workbook.

 

Get FREE Advanced Excel Exercises with Solutions!

Tags:

Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

About ExcelDemy.com

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact | Privacy Policy | TOS

ExcelDemy Consulting Services

Submit Excel Task

 

See Our Review attrustpilot review

ExcelDemy Online Training


Join ExcelDemy Forum
DMCA.com Protection Status

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo