PPT Excel: How to find the last used Row, Column, and Cell

Do you need to find the last used row, column, or cell in your excel using VBA? In this post, you will learn how to find the last used cell in your worksheet. It is one of the basic operations to find the last used cell to assign an upper limit to your array for your automation. There are different methods you can use to find the last used cells in excel.

Suppose you have received the below sample data and need to find the last row, column, and cell address for your report

VBA Script to find the last used row, column, and cell

Sub returnLastUsedCell()
'Variable Declaration
Dim lstRow As Long, lstCol As Long, lstCell As String
'To Find Last Used Row
lstRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row
'To Find Last Used Column
lstCol = Range("A1").SpecialCells(xlCellTypeLastCell).Column
'To Find Last Used Cell
lstCell = Range("A1").SpecialCells(xlCellTypeLastCell).Address

'Output print the last used row/column/cell
MsgBox "Last Used Row : " & lstRow  & "Last Used Column : " & lstCol  & "Last Used Cell : " & lstCell
End Sub

Copy and Paste the above VBA script into your editor and run the script

Please follow the below steps to add auto shapes and assign the above VBA script to find the last used row, column, and cell address

1. Add a shape and type Return details of Last Used Cell on your worksheet  
2. Right-click on Return details of Last Used Cell and select Assign Macro…

3. Choose the returnLastUsedCell macro from the list, you can see a list of macros are available in your workbook

4. Save your excel file as Excel Macro-Enabled Workbook

5. Click Return details of Last Used Cell to execute the assigned above VBA code.  Code will popup the output for the given sample data

Conclusions

Suppose you want to map with employee id and add a new column to your existing excel data table, you need to find the last used column and add the new column. You have to find the last row if you need to append two tables in your excel workbook. These are very basic operations in excel VBA when you start automating your reports or creating a dashboard.

Let me know in the comments below if you need any articles in excel VBA for your reports or projects.