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.
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.
Read More: How to Find Row Number Using VBA in Excel
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.
Read More: Excel VBA: Find String in Column and Return Row Number
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
- Find String in Column and Return Row Number in Excel
- How to Get Row Number from Range with Excel VBA
- How to Get Row Number of Current Cell in Excel
- How to Use Range with Variable Row Number in Excel
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.
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.