Sometimes when we work with a large named data range, we may need to find out the address of the last cell in the range. We can do this manually by scrolling but it is a tedious and time-consuming exercise.
In this tutorial, we will explore 5 methods that can use to easily find out the address of the last cell in a range.
Tutorial Content
Example
We will use the following dataset named Bank_Accounts to explain all the methods:
We are going to use the following functions in our examples.
Function | Explanation |
ADDRESS | Creates a cell reference as text, given specified row and column numbers. |
ROW | Returns the row number of a reference. |
ROWS | Returns the number of rows in a reference or an array. |
COLUMN | Returns the column number of a reference. |
COLUMNS | Returns the number of columns in a reference or an array. |
MAX | Returns the largest value in a set of values |
Method 1: Use ADDRESS, ROW, ROWS, COLUMN, and COLUMNS functions
In the first method, We can use a formula that combines the ADDRESS, ROW, ROWS, COLUMN, and COLUMNS functions to return the address of the last cell in a range.
To apply this method, we use the following steps:
- In the worksheet that contains the Bank_Accounts range, select cell I2 and type in the formula:
1 |
=ADDRESS(ROW(Bank_Accounts)+ROWS(Bank_Accounts)-1,COLUMN(Bank_Accounts)+COLUMNS(Bank_Accounts)-1) |
- Press the Enter key or click Enter option on the Formula Bar to enter the formula:
The result is loaded:
Explanation of the formula
1 |
ROW(Bank_Accounts)+ROWS(Bank_Accounts)-1 |
The ROW function gives us an array of all the row numbers in the range: {2;3;4;5;6;7;8;9}. We need to “reverse” these numbers by the total row count minus 1 so that the first number in the array becomes the last row number. To find the total row count, we use the ROWS function and subtract 1 from its result: (8-1=7). Then, we add 7 to each element of the initial array to do the required reversing: {2;3;4;5;6;7;8;9} + 7 = {9;10;11;12;13;14;15;16}.
1 |
COLUMN(Bank_Accounts)+COLUMNS(Bank_Accounts)-1 |
The COLUMN function returns an array of all the column numbers in the range: {1;2;3;4;5;6;7}. We need to “reverse” these numbers by the total row count minus 1 so that the first number in the array becomes the last row number. To find the total column count, we use the COLUMNS function and subtract 1 from its result: (7-1=6). Then, we add 6 to each element of the initial array to do the required reversing: {1;2;3;4;5;6;7} + 6 = {7;8;9;10;11;12;13;14}.
With the above arrays of row and column numbers, the formula then becomes:
1 |
=ADDRESS({9;10;11;12;13;14;15;16},{7;8;9;10;11;12;13;14}) |
The ADDRESS function then creates an array of cell addresses, but returns only the first one =ADDRESS({9},{7}) corresponding to the last cell in the range which is cell $G$9.
Method 2: Use ADDRESS, MAX, ROW, and COLUMN functions
In this method, we create a formula that combines the ADDRESS, MAX, ROW, and COLUMN functions to return the address of the last cell in a range.
To apply this method, we use the following steps:
- In the worksheet that contains the Bank_Accounts range, select cell I2 and type in the formula:
1 |
=ADDRESS(MAX(ROW(Bank_Accounts)),MAX(COLUMN(Bank_Accounts))) |
- Since this is an array formula, we press Ctrl + Shift + Enter to enter it. In Excel 365 we can press the Enter key or the Enter option on the Formula Bar.
The result is loaded:
When we look in the Formula Bar, we notice that Excel has enclosed the formula in curly braces “{ }”.
This is Excel’s way of identifying array formulas.
Explanation of the formula
1 |
MAX(ROW(Bank_Accounts) |
The ROW function returns an array of all the row numbers in the range: {2;3;4;5;6;7;8;9}. This array is then passed to the MAX function which returns the largest value in the array which is 9.
1 |
MAX(COLUMN(Bank_Accounts) |
The COLUMN function returns an array of all the column numbers in the range: {1;2;3;4;5;6;7}. This array is then passed to the MAX function which in turn returns the largest number in the array which is 7.
The formula then becomes =ADDRESS({9},{7}) and the ADDRESS function returns $G$9, which is the address of the last cell in the range.
Method 3: Use CELL, INDEX, ROWS, and COLUMNS functions
In this method, we apply a formula that combines the CELL, INDEX, ROWS, and COLUMNS functions to return the address of the last cell in a range.
The ROWS and COLUMNS functions were explained in Method 1.
The CELL function returns information about the formatting, location, or contents of the first cell according to the sheet’s reading order, in a reference.
The INDEX function returns a value or reference of the cell at the intersection of a particular row or column in a given range.
We use the followings steps to apply this method:
- In the worksheet that contains the Bank_Accounts range, select cell I2 and type in the formula:
1 |
=CELL("address",INDEX(Bank_Accounts,ROWS(Bank_Accounts),COLUMNS(Bank_Accounts))) |
- Press Enter key to enter the formula.
The result is loaded:
Explanation of the formula
1 |
=CELL("address",INDEX(Bank_Accounts,ROWS(Bank_Accounts),COLUMNS(Bank_Accounts))) |
The CELL function takes two arguments: Info_type and reference. The info_type argument indicates the type of information requested. In this case, we want the “address” of the reference.
1 |
INDEX(Bank_Accounts,ROWS(Bank_Accounts),COLUMNS(Bank_Accounts)) |
The INDEX function returns the cell reference of the cell at the intersection of the last row (9) and last column (7)of the data range.
The CELL reference returns $G$9 as the address of the last cell in the range.
Method 4: Use ADDRESS, ROW(S), COLUMN(S), and INDEX functions
We can use the formula that combines the ADDRESS, ROW, ROWS, INDEX, COLUMN, and COLUMNS functions to return the last cell in a range.
We use the following steps:
In the worksheet that contains the Bank_Accounts range, select cell I2 and type in the formula:
1 |
=ADDRESS(ROW(INDEX(Bank_Accounts,ROWS(Bank_Accounts),1)),COLUMN(INDEX(Bank_Accounts,1,COLUMNS(Bank_Accounts)))) |
- Press the Enter key to load the result.
Explanation of the formula
1 |
ROW(INDEX(Bank_Accounts,ROWS(Bank_Accounts),1)) |
The ROWS function returns the number of rows in the range which is 9. The INDEX function then returns the cell reference of the cell at the intersection of row number 9 and column number 1. The ROW function returns the row number of the cell reference which is 9.
1 |
COLUMN(INDEX(Bank_Accounts,1,COLUMNS(Bank_Accounts))) |
The COLUMNS function returns the number of columns in the range which is 7. The INDEX function then returns the cell reference of the cell at the intersection of column number 7 and row number 1. The COLUMN function returns the column number of the cell reference which is 7.
The formula then becomes =ADDRESS(9,7) and the ADDRESS function returns $G$9 which is the address of the last cell in the range.
Method 5: Use Excel VBA
We can use Excel VBA to display the address of the last cell in a range.
We use the following steps to apply this method:
- In the active worksheet that contains the Bank_Accounts range, press Alt + F11 to open the Visual Basic Editor (VBE). Alternatively, click Developer >> Code >> Visual Basic.
- Right-click the ThisWorkbook object and select Insert >> Module from the shortcut menu:
Alternatively, we can click Insert >> Module on the menu:
- In the inserted module, type in the following code:
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub lastCellAddress() Dim lRow As Long Dim lColumn As Long Dim lcAddress As String lRow = Range("A1").End(xlDown).Row lColumn = Range("A1").End(xlToRight).Column lcAddress = Cells(lRow, lColumn).Address MsgBox lcAddress End Sub |
- Save the procedure and save the workbook as a macro-enabled workbook.
- Click anywhere in the procedure and press F5 to run the code.
A message box pops up displaying the address of the last cell in the range:
Explanation of the procedure
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub lastCellAddress() Dim lRow As Long Dim lColumn As Long Dim lcAddress As String lRow = Range("A1").End(xlDown).Row lColumn = Range("A1").End(xlToRight).Column lcAddress = Cells(lRow, lColumn).Address MsgBox lcAddress End Sub |
Two Long variables and one String variable are declared.
1 |
lRow = Range("A1").End(xlDown).Row |
This statement returns the row number of the last cell in the range and assigns it to the lRow variable. In this case, it is row number 9.
1 |
lColumn = Range("A1").End(xlToRight).Column |
This statement returns the column number of the last cell in the range and assigns it to the lColumn variable. In this case, it is column number 7.
1 |
lcAddress = Cells(lRow, lColumn).Address |
The address of the cell at the intersection of row number 9 and column number 7 is assigned to the lcAddress variable.
1 |
MsgBox lcAddress |
The MsgBox function displays a message box displaying the value in variable lcAddress which is $G$9, the address of the last cell in the range.
Conclusion
When we work with a large named data range, we may need to find out the address of the last cell in the range.
In this tutorial, we have explored 5 methods we can use to easily locate the address of the last cell in a named range. The methods involve the use of Excel functions and Excel VBA.