Return last row number in a range
This tutorial shows how to return the last row number from a selected range using an Excel formula or VBA
Example: Return last row number in a range
|
This formula uses the Excel ROW functions to return the first row number and the Excel ROWS function to return the total number of rows in the selected range. Adding these together and subtracting a value of one will return the last row number in a selected range which returns a value of 10 in this example.
|
Sub Return_last_row_number_in_range()
'declare variables
Dim ws As Worksheet
Dim rng As Range
Dim ws As Worksheet
Dim rng As Range
Set ws = Worksheets("Analysis")
Set rng = ws.Range("B5:D10")
Set rng = ws.Range("B5:D10")
'return the last row number in a range
ws.Range("F5") = rng.Row + rng.Rows.Count - 1
ws.Range("F5") = rng.Row + rng.Rows.Count - 1
End Sub
EXPLANATION
This tutorial shows how to return the last row number from a selected range through the use of an Excel formula or VBA.
Using the ROW and ROWS function in both the Excel and VBA methods it will return the last row number in a selected range. The ROW function returns the first row number from the selected cell or range and the ROWS function returns the total number of rows in a range. Adding these two values and subtracting a value of one will return the last row number in a range.
FORMULA
=ROW(range)+ROWS(range)-1
=ROW(range)+ROWS(range)-1
ARGUMENTS
range: A range of cells for which you want to return the last row number.
range: A range of cells for which you want to return the last row number.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return last column number in a range | How to return the last column number 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 | |
| ROWS Function | The Excel ROWS function returns the number of rows in a specified array |