Sometimes when using Excel spreadsheets you need to find the Last Used cell in a worksheet or a column of data. This is useful as in large lists of information you can quickly work out where to either start adding new information or where to look for missing data in a table of information.
There are a number of options to find the Last Used cell in a worksheet. You can use direct worksheet navigation keys to go to the bottom of column or VBA can be used to determine the last used row in a column. In this post we will describe both methods.
Option 1 – Navigation Keys
We have the following sample data table with missing information in some of the columns. We will now use standard navigation keys to arrive at the Last Used cell for each column. In a real world example the data table would have thousands of rows of information and ability to quickly find the Last Used cell is very useful.
Select the cell A5, which is the start of the Start Date column. Now use the CTRL + Arrow Down key this will take you to the Last Used cell in that column. Repeat that for the next column First Name and the selected cell will navigate to cell B6, hit the Arrow Down key again and it will navigate to B10.
This method only goes to the first Unused cell in a column not the Last Unused cell. You need to keep repeating the process until you arrive at the Last Unused cell.
Another approach is to use CTRL + END key this will take you to the cell that has the Last Used column and row in the worksheeet. Start by selecting cell A5, now use CTRL + END and the selected cell will be F13. This then tells you that the last used column in the worksheet is F and the last used row in the worksheet is 13.
Option 2 – Macro to Find Last Used Cell
Create a workbook with a similar table of information to the example above. Then start the Visual Basic Editor (Alt + F11). Right Click in the project explorer window and select Insert > Module. Copy the following code into the new Module
Sub LastUsedCellInColumn() 'This routine will calculate the Row number of the Last Used Cell in all the Columns of the worksheet Dim myRange As Range Dim i As Integer Set myRange = Worksheets("Sheet1").Range("StartHeading") i = 0 Do While myRange.Offset(0, i).Value <> "" myRange.Offset(-2, i).Value = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, _ myRange.Offset(0, i).Column).End(xlUp).Row i = i + 1 Loop End Sub
Now to run the macro, within the worksheet you have created above. Select the Developer Tab, then Macros. Select the macro LastUsedCellInColumn, then select Run. The macro will then execute.
After the macro has completed the following Last Row numbers will be shown above each of the Column headings. This is very useful and can be used to perform other actions on those columns and cells.
In a future post we will demonstrate how this macro can be changed to calculate the last used cell in a row rather than a column. If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.