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**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 IfEnd 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 StringDim 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 ThenMsgBox ("Value Not found")ElseMsgBox ("Row Number is: " & rowX.Row)End IfEnd 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 (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**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 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 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 IntegerDim LastRow As LongDim 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 ThenIf RowNoList = "" ThenRowNoList = RowNoList & cel.RowElseRowNoList = RowNoList & ", " & cel.RowEnd IfEnd IfNext 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 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 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 RangeDim 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.

**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**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 LongRow_1 = Columns(3).Find(What:=26).RowMsgBox "Row Number is: " & Row_1End 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.

**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**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 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 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 WorksheetDim Row_match As LongDim J As LongDim Value_Search As StringDim 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 100If StrComp(Data_array(J, 3), Value_Search, vbTextCompare) = 0 ThenRow_match = JExit ForEnd IfNext 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.

**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? ›**

**Get Row Number with VBA with Cell Change**

- Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Dim rownum As Integer.
- rownum = ActiveCell. Row.
- MsgBox "You are currently On a row number " & rownum.

**How do you get the row number based on the cell value? ›**

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? ›**

**How To Get Row Number By A Single Click Using VBA In Microsoft Excel 2010**

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

**How do you use row () in VBA? ›**

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.

**How do I return a row of values in Excel? ›**

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.