Return address of last cell in a range
This tutorial shows how to return the address of a last cell in a specific range using an Excel formula or VBA
Example: Return address of last cell in a range
Sub Address_of_last_cell_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:C10")
Set rng = ws.Range("B5:C10")
'return the address of the last cell in a specified range
ws.Range("E5") = rng(1).Cells(rng.Rows.Count, rng.Columns.Count).Address
ws.Range("E5") = rng(1).Cells(rng.Rows.Count, rng.Columns.Count).Address
End Sub
EXPLANATION
This tutorial shows how to return an address of a last cell in a range through the use of an Excel formula or VBA.
The Excel formula uses the ADDRESS, ROW, ROWS, COLUMN and COLUMNS functions to return the address of the last cell in the specified range, whilst the VBA method uses only the Address function.
FORMULA
=ADDRESS(ROW(range)+ROWS(range)-1,COLUMN(range)+COLUMNS(range)-1)
=ADDRESS(ROW(range)+ROWS(range)-1,COLUMN(range)+COLUMNS(range)-1)
ARGUMENTS
range: The range from which to return the address of the last cell.
range: The range from which to return the address of the last cell.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Return address of a specific cell | How to return the address of a specific cell using Excel and VBA | |
| Return address of active cell | How to return the address of an active cell using Excel and VBA | |
| Return address of first cell in a range | How to return the address of a first cell in a specific range using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| ADDRESS Function | The Excel ADDRESS function returns a cell reference as a string, based on a row and column number | |
| 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 | |
| COLUMN Function | The Excel COLUMN function returns the first column number of the selected reference | |
| COLUMNS Function | The Excel COLUMNS function returns the number of columns in a specified array |