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
Download Practice Workbook
5 Suitable Methods to Return Row Number of a Value Using VBA in Excel
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
Related Articles
Download Practice Workbook
Download the practice workbook below.
Return Number of Value.xlsm
5 Suitable Methods to Return Row Number of a Value Using VBA in Excel
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 Codegroup.
- It will open up the Visual Basicwindow.
- Then, go to the Insert tab at the top.
- After that, select the Moduleoption.
- 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 IfEnd Sub- Then, close the Visual Basicwindow.
- After that, go to the Developer tab on the ribbon.
- Select the Macros option from the Codegroup.
- Then, the Macro dialog box will appear.
- Select GetRowNum1 from the Macro namesection.
- 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 StringDim rowX As RangeNext, 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 ThenMsgBox ("Value Not found")ElseMsgBox ("Row Number is: " & rowX.Row)End IfEnd SubIn 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 SubFinally, end the sub-procedure of the macro
Read More: How to Find Row Number Using VBA in Excel (4 Macros)
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 Codegroup.
- It will open up the Visual Basicwindow.
- Then, go to the Insert tab at the top.
- After that, select the Moduleoption.
- As a result, a Module code window will appear.
- Write down the following code.
Sub GetRowNum2()Dim MyVal As IntegerDim LastRow As LongDim RowNoList As StringMyVal = 84LastRow = Cells(Rows.Count, "C").End(xlUp).RowFor Each cel In Range("C2:C" & LastRow) If cel.Value = MyVal ThenIf RowNoList = "" ThenRowNoList = RowNoList & cel.RowElseRowNoList = RowNoList & ", " & cel.RowEnd IfEnd IfNext celMsgBox "Row Number is: " & RowNoListEnd Sub- Then, close the Visual Basicwindow.
- After that, go to the Developer tab on the ribbon.
- Select the Macros option from the Codegroup.
- Then, the Macro dialog box will appear.
- Select GetRowNum2 from the Macro namesection.
- 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 IntegerDim LastRow As LongDim RowNoList As StringNext, declare the necessary variable for the macro.
MyVal = 84After that define the value from where you want to get the row number.
LastRow = Cells(Rows.Count, "C").End(xlUp).RowThen, 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 ThenIf RowNoList = "" ThenRowNoList = RowNoList & cel.RowElseRowNoList = RowNoList & ", " & cel.RowEnd IfEnd IfNext celAfter 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: " & RowNoListIn this message box, a message is displayed in a dialog box along with a row number.
End SubFinally, 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 Codegroup.
- It will open up the Visual Basicwindow.
- Then, go to the Insert tab at the top.
- After that, select the Moduleoption.
- As a result, a Module code window will appear.
- Write down the following code.
Sub GetRowNum3()Dim SearchRang As RangeDim FRow As RangeSet SearchRang = Range("C1", Range("C65536").End(xlUp))Set FRow = SearchRang.Find(65, LookIn:=xlValues, LookAt:=xlWhole)MsgBox "Row Number is: " & FRow.RowEnd Sub- Then, close the Visual Basicwindow.
- After that, go to the Developer tab on the ribbon.
- Select the Macros option from the Codegroup.
- Then, the Macro dialog box will appear.
- Select GetRowNum3 from the Macro namesection.
- 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 RangeDim FRow As RangeNext, 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.RowIn this message box, a message is displayed in a dialog box along with a row number.
End SubFinally, end the sub-procedure of the macro.
Read More: Find String in Column and Return Row Number in Excel (7 Ways)
Similar Readings
- How to Get Row Number from Cell Value in Excel (5 Methods)
- Get Row Number from Range with Excel VBA (9 Examples)
- How to Get Row Number of Current Cell in Excel (4 Quick Ways)
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 Codegroup.
- It will open up the Visual Basicwindow.
- Then, go to the Insert tab at the top.
- After that, select the Moduleoption.
- As a result, a Module code window will appear.
- Write down the following code.
Sub GetRowNum4()Dim Row_1 As LongRow_1 = Columns(3).Find(What:=26).RowMsgBox "Row Number is: " & Row_1End Sub- Then, close the Visual Basicwindow.
- After that, go to the Developer tab on the ribbon.
- Select the Macros option from the Codegroup.
- Then, the Macro dialog box will appear.
- Select GetRowNum4 from the Macro namesection.
- 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 LongNext, declare the necessary variable for the macro.
Row_1 = Columns(3).Find(What:=26).RowThis variable will contain the row number of the range.
MsgBox "Row Number is: " & Row_1In this message box, a message is displayed in a dialog box along with a row number.
End SubFinally, end the sub-procedure of the macro.
Read More: Excel VBA: Find String in Column and Return Row Number
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 Codegroup.
- It will open up the Visual Basicwindow.
- Then, go to the Insert tab at the top.
- After that, select the Moduleoption.
- As a result, a Module code window will appear.
- Write down the following code.
Sub GetRowNum5()Dim WS1 As WorksheetDim Row_match As LongDim J As LongDim Value_Search As StringDim Data_array As VariantSet WS1 = Worksheets("Applying VBA StrComp Function")Data_array = WS1.Range("A1:E100")Value_Search = 56For J = 1 To 100If StrComp(Data_array(J, 3), Value_Search, vbTextCompare) = 0 ThenRow_match = JExit ForEnd IfNext JMsgBox "Row Number is: " & Row_matchEnd Sub- Then, close the Visual Basicwindow.
- After that, go to the Developer tab on the ribbon.
- Select the Macros option from the Codegroup.
- Then, the Macro dialog box will appear.
- Select GetRowNum4 from the Macro namesection.
- 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 WorksheetDim Row_match As LongDim J As LongDim Value_Search As StringDim Data_array As VariantNext, declare the necessary variable for the macro.
Set WS1 = Worksheets("Applying VBA StrComp Function")After that, specify the worksheet name.
For J = 1 To 100If StrComp(Data_array(J, 3), Value_Search, vbTextCompare) = 0 ThenRow_match = JExit ForEnd IfNext JIn 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_matchIn this message box, a message is displayed in a dialog box along with a row number.
End SubFinally, end the sub-procedure of the macro.
Read More: VBA Range with Variable Row Number in Excel (4 Examples)
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. Don’t forget to visit our Exceldemy page.
Related Articles
- How to Use Variable Row Number as Cell Reference in Excel
- How to Return Row Number of a Cell Match in Excel (7 Methods)
- [Fixed!] Missing Row Numbers and Column Letters in Excel (3 Solutions)
- How to Increment Row Number in Excel Formula (6 Handy Ways)
FAQs
How do you count the number of rows with a specific value in VBA? ›
To count rows using VBA, you need to define the range from which you want to count the rows and then use the count and rows property to get the count of the row from that range. You can also use a loop to count rows where you have data only.
How do I return a row number in VBA? ›- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Dim rownum As Integer.
- rownum = ActiveCell. Row.
- MsgBox "You are currently On a row number " & rownum.
Supposing you want to know the row number of “ink” and you already know it locates at column A, you can use this formula of =MATCH("ink",A:A,0) in a blank cell to get it's row number. After entering the formula, and then press the Enter key, it will show the row number of the cell which contains "ink".
How do you return the row number of the range? ›If the reference is a range of cells, ROW function returns the row number of the top-most row in the specified range. For example, =ROW(B5:D10) would return 5 as the top-most row is B2 for which the row number is 5.
How do you count the number of rows with unique values in Excel? ›You can use the combination of the SUM and COUNTIF functions to count unique values in Excel. The syntax for this combined formula is = SUM(IF(1/COUNTIF(data, data)=1,1,0)). Here the COUNTIF formula counts the number of times each value in the range appears.
How do I get the row number of a selected cell in VBA? ›- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Dim rownumber As Integer.
- rownumber = ActiveCell.Row.
- If ActiveCell.Value <> "" Then.
- MsgBox "You have clicked on row number " & rownumber.
- End If.
To insert a row using a VBA code, you need to use the “Entire Row” property with the “Insert” method. With the entire row property, you can refer to the entire row using a cell and then insert a new row there. By default, it will insert a single row before the cell that you have mentioned.
How do you return row number within range in Excel? ›Use the ROW function to number rows
In the first cell of the range that you want to number, type =ROW(A1). The ROW function returns the number of the row that you reference. For example, =ROW(A1) returns the number 1. across the range that you want to fill.
To return an entire row you need to use array returning functions like INDEX or OFFSET. Both of these functions can return arrays, as well as single values, which can be used in other functions like SUM, AVERAGE or even another INDEX or OFFSET.
What is the formula for the number of rows? ›To use the COUNTA function to count rows in your Excel spreadsheet, follow these steps: Click on an empty cell where you want to display the row count. Type the following formula: =COUNTA(A:A) Press Enter to calculate the row count.
Which function return the number of rows selected? ›
The COUNT() function returns the number of rows that matches a specified criterion.
What is the formula that returns the row? ›The ROW function in Excel is used to return the row number of a specific cell or range. It takes an optional reference argument that specifies the cell or range for which you want to return the row number. For example, the formula "=ROW(A43)" returns the row number of cell A43 which is 43.
How do I select all rows with a certain value in Excel VBA? ›We'll use the VBA macro to accomplish this task. In the code, we used the For… Next loop, If statement, and VBA Union function to select row based on cell value in Excel. After running the code, all the desired rows (rows 5, 8, 9, 12, 14) get selected at the same time.
How do I count the number of rows in an array in VBA? ›To count rows. Depending on the circumstance, you can use the COUNTA, COUNT, COUNTBLANK, or COUNTIF functions. read more, we need to use the RANGE object. In this object, we need to use the ROWS object.
How do you count the number of cells with specific text in a row in Excel? ›Enter the formula "=COUNTIF(range, "text")" in a cell for the result. Replace "range" with the column range to search (for example, A1:A10) and "text" with the specific text string to count.
How do I count specific characters in VBA? ›The VBA method uses the Len and Substitute functions, similar to the Excel method, to count the number of specific characters in a cell. text: The string from which to extract the characters. count_for: The character(s) that are to be counted from the selected text.