Skip to content
Related Articles

Related Articles

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

View Discussion
Improve Article
Save Article
  • 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:

Variable Data Type Comments
LastRow Long Find and store last used row
LastCol Long store last used column
LastCell String store 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!