Skip to content
Related Articles

Related Articles

Improve Article
Save Article
Like Article

How to Find the Last Used Row and Column in Excel VBA?

  • Last Updated : 09 Nov, 2021

We use  Range.SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet.

Sample Data:

Sample Data

Syntax:

expression.SpecialCells (Type, Value)

Eg: To return the last used cell address in an activesheet. ActiveSheet.Range(“A1”).SpecialCells(xlCellTypeLastCell).Address

VBA Code:

Declaring Variables:

VariableData TypeComments
LastRowLongFind and store last used row
LastColLongstore last used column
LastCellStringstore last used cell address
'Variable Declaration

Dim LastRow As Long, LastCol As Long, LastCell As String

Use SpecialCells function to find last used row/column/cell 

'Find Last Used Row
LastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row

'Find Last Used Column
LastCol = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column

'Find Last Used Cell
LastCell = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address

Concatenate all three variables (LastRow/LastCol/LastCell), add a new line between variables use Chr(10).   Show the final output in an Excel Message box.

'Display the last used row/column/cell

MsgBox "Last Used Row : " & LastRow & Chr(10) & "Last Used Column : " & LastCol & Chr(10) & "Last Used Cell : " & LastCell

Implementation:

Follow the below steps to find the Last Used Row and Last Used Column in Excel VBA

Step 1: Add a shape (Find Last Row/Column/Cell) on your worksheet.  

Step 2: Right-click on “Find Last Row/Column/Cell” and “Assign Macro..”



Step 3: Select “findLastUsedCell”, you can see a list of macros if available in your workbook

Step 4: Save your excel file as “Excel Macro-Enabled Workbook” *.xlsm

Step 5: Click “Find Last Row/Column/Cell” to execute the VBA code.  Code will popup below output for the above given example (Output).

Output:

My Personal Notes arrow_drop_up
Recommended Articles
Page :

Start Your Coding Journey Now!