Return nth row number in a range
This tutorial shows how to return the nth row number from a selected range using an Excel formula or VBA
Example: Return nth row number in a range
|
=ROW(B5:D10)+F5-1
|
This formula uses the Excel ROW function to return the row number of the first cell in the selected range and then adds the nth row number (subtracting one) that you want to return. In this example we are returning the second row in the range which returns a value of 6.
|
Sub Return_nth_row_number_in_range()
'declare variables
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'return the nth row number in a range
ws.Range("G5") = ws.Range("B5:D10").Row+ws.Range("F5")-1
ws.Range("G5") = ws.Range("B5:D10").Row+ws.Range("F5")-1
End Sub
EXPLANATION
This tutorial shows how to return the nth row number from a selected range through the use of an Excel formula or VBA.
Using the ROW function in both the Excel and VBA methods it will return the first row number in a selected range. You then add the nth row number and subtract one.
FORMULA
=ROW(range)+row_number-1
=ROW(range)+row_number-1
ARGUMENTS
range: A range of cells for which you want to return the first row number.
row_number: The nth row number that you want to return.
range: A range of cells for which you want to return the first row number.
row_number: The nth row number that you want to return.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return first row number in a range | How to return the first row number from a selected range using Excel and VBA | |
| Return nth column number in a range | How to return the nth column number from a selected range using Excel and VBA | |
| Return first column number in a range | How to return the first column number from a selected range using Excel and VBA | |
| Return first column name in a range | How to return the first column name from a selected range using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| ROW Function | The Excel ROW function returns the first row number of the selected reference |