Excel VBA: Return Row Number of Value (5 Suitable Methods)

Get FREE Advanced Excel Exercises with Solutions!

Sometimes, you want to return the row number of a given value. In that case, VBA can be the best option. In Visual Basic, you can use several conditions to get the row number. This article will show how to return the row number of a value using Excel VBA. I hope you find this article interesting and gain lots of knowledge.

Table of Contents hide

Excel VBA to Return Row Number of Value: 5 Suitable Methods

1. Using Input Box

2. Utilizing VBA If Statement

3. Use of VBA Find Method

4. Using Columns Property

5. Applying VBA StrComp Function

Conclusion

Excel VBA to Return Row Number of Value: 5 Suitable Methods

To return the row number of a value using Excel VBA code, we have found five different methods. All of these VBA codes are fairly easy to understand and user-friendly. In these five methods, we utilize the input box, If condition, Find method, column property, and StrComp function in VBA. Here, we would like to show all the processes and explanations of each code to have a better understanding.

1. Using Input Box

In this first method, we will utilize the input box in the VBA code to return the row number of a value. Here, we will basically show how to write the VBA code using the input box and finally, show the output of that VBA code. Before doing anything, you need to enable the Developer tab on the ribbon. To do this, follow the link How to Show the Developer Tab on the Ribbon. To use this VBA code, follow the steps carefully.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
After that, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select GetRowNum1 from the Macro name section.
After that, click on Run.

As a result, an input box will appear where you can add values.
Then, click on OK.

As a result, we get the row number of that value as our output.

🔎 VBA Code Explanation

Sub GetRowNum1()

First of all, provide a name for the sub-procedure of the macro.

 Dim Marks As String
 Dim rowX As Range

Next, declare the necessary variable for the macro.

  Marks = InputBox("What is Value?")

Then, provide the user a dialog box to enter information. After that, return the data they have entered.

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

specify the cell position where the input information from the input box will be selected.

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

In the first line of this piece of the code, we inserted 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 appear.

End Sub

Finally, end the sub-procedure of the macro.

2. Utilizing VBA If Statement

Our Next method is based on using the VBA If statement. Using the If statement, we get the row number of a value as the output. To understand the code clearly, follow the steps.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
After that, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select GetRowNum2 from the Macro name section.
After that, click on Run.

As a result, we get the row number of that value as our output.

🔎 VBA Code Explanation

Sub GetRowNum2()

First of all, provide a name for the sub-procedure of the macro.

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

Next, declare the necessary variable for the macro.

MyVal = 84

After that define the value from where you want to get the row number.

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

Then, define the last row of that certain column where you would think to have 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

After that, we consider the For statement. In this For statement, we insert the If condition. The condition implies if the cell value is equal to the given value then it will return the row number. Otherwise, it will return a blank. Then, go to the next cell and do the same loop.

MsgBox "Row Number is: " & RowNoList

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

End Sub

Finally, end the sub-procedure of the macro.

3. Use of VBA Find Method

Our third method is based on using the VBA find method. In this code, we will find the value and get the row number from there. As an output, we get the required row number. To understand properly we will discuss the code and explanation in the following steps.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
After that, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select GetRowNum3 from the Macro name section.
After that, click on Run.

As a result, we get the row number of that value as our output.

🔎 VBA Code Explanation

Sub GetRowNum3()

First of all, provide a name for the sub-procedure of the macro.

Dim SearchRang As Range
Dim FRow As Range

Next, declare the necessary variable for the macro.

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

Then, set the search range of the given value where it could appear.

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

After that, set the find row of a certain value that you need to assign in this step.

MsgBox "Row Number is: " & FRow.Row

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

End Sub

Finally, end the sub-procedure of the macro.

Similar Readings

4. Using Columns Property

Next, we will show another VBA code to find a value in a column and return the row number using the column property by applying the VBA code in Excel. To understand this properly, follow the steps and explanation of the code.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
After that, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select GetRowNum4 from the Macro name section.
After that, click on Run.

As a result, we get the row number of that value as our output.

🔎 VBA Code Explanation

Sub GetRowNum4()

First of all, provide a name for the sub-procedure of the macro.

Dim Row_1 As Long

Next, declare the necessary variable for the macro.

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

This variable will contain the row number of the range.

 MsgBox "Row Number is: " & Row_1

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

End Sub

Finally, end the sub-procedure of the macro.

5. Applying VBA StrComp Function

Next, we will find the row number of a given value by using the VBA StrComp function. In this method, we will utilize the SteComp function and also provide a clear explanation of this code. To understand it properly, follow the steps.

Steps

First, go to the Developer tab on the ribbon.
Then, select the Visual Basic option from the Code group.

It will open up the Visual Basic window.
Then, go to the Insert tab at the top.
After that, select the Module option.

As a result, a Module code window will appear.
Write down the following code.

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

Then, close the Visual Basic window.
After that, go to the Developer tab on the ribbon.
Select the Macros option from the Code group.

Then, the Macro dialog box will appear.
Select GetRowNum4 from the Macro name section.
After that, click on Run.

As a result, we get the row number of that value as our output.

🔎 VBA Code Explanation

Sub GetRowNum5()

First of all, provide a name for the sub-procedure of the macro.

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

Next, declare the necessary variable for the macro.

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

After that, specify 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

In the first line of this piece of the code, we inserted the For statement. Now, starts looping from the declared column. If the iteration variable finds the value 56 in any row of the declared column, then it returns the row number.

MsgBox "Row Number is: " & Row_match

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

End Sub

Finally, end the sub-procedure of the macro.

Download Practice Workbook

Download the practice workbook below.

Return Number of Value.xlsm

Conclusion

We have shown five different and effective VBA methods to return the row number of a value in Excel. All of these VBA codes are effective and user-friendly. In this article, we included all possible explanations of the VBA codes that we use to solve the method. If you have any questions, feel free to ask in the comment box.

Excel VBA: Return Row Number of Value (5 Suitable Methods)

Excel VBA to Return Row Number of Value: 5 Suitable Methods

1. Using Input Box

2. Utilizing VBA If Statement

3. Use of VBA Find Method

4. Using Columns Property

5. Applying VBA StrComp Function

Conclusion

Leave a reply Cancel reply