Excel VBA : Find Last Used Row and Last Used Column

This tutorial provides you VBA codes to find the last used row and last used column in a worksheet.

1. Determine the last used row in a column
The following macro returns the last used row number in column A

Sub lastusedrow()
Dim last As Long
With ActiveSheet
last = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
MsgBox "Last used row number in column A is " & last
End Sub

How to edit the above macro

1. Specify worksheet name
To find the last used row in a column in worksheet "Sheet2", you can replace
With Activesheet with With Sheets("Sheet2")

2. Specify column name

To find the last used row in column B , you can replace

.Cells(.Rows.Count, "A") with .Cells(.Rows.Count, "B")

How to use the above macro
1. Open MS Excel
2. Press ALT + F11 to open visual basic editor
3. Go to Insert >> Module to insert a module
4. Copy the above code and paste it into the module (code window)

2. Determine the last used row in a worksheet (excluding formulas displaying blank cell)
The following macro returns the last used row number in worksheet "Sheet1". It includes rows with data or a formula displaying data. It ignores cells containing formulas displaying the empty string i.e. ="".

Sub lastusedrow1()
Dim last As Long
last = Worksheets("Sheet1").Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row
MsgBox "Last used row number in sheet1 is " & last
End Sub

How to edit the above macro

Specify worksheet name
To find the last used row in worksheet "Sheet2", you can replace
Worksheets("Sheet1") with Worksheets("Sheet2")

How to use the above macro
1. Open MS Excel
2. Press ALT + F11 to open visual basic editor
3. Go to Insert >> Module to insert a module
4. Copy the above code and paste it into the module (code window)

3. Determine the last used row in a worksheet (including formulas displaying blank cell)
The following macro returns the last used row number in worksheet "Sheet1". It includes rows with data or a formula displaying empty and non-empty string values.

Sub lastusedrow1()
Dim last As Long
last = Worksheets("Sheet1").Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
MsgBox "Last used row number in sheet1 is " & last
End Sub

How to edit the above macro

Specify worksheet name
To find the last used row in worksheet "Sheet2", you can replace
Worksheets("Sheet1") with Worksheets("Sheet2")

4. Determine the last used column in a row
The following macro returns the last used column number in row 1

Sub lastusedcolumn()
Dim last As Long
With ActiveSheet
last = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With
MsgBox "Last used column number in row 1 is " & last
End Sub

How to edit the above macro

1. Specify worksheet name
To find the last used column in a row in worksheet "Sheet2", you can replace
With Activesheet with With Sheets("Sheet2")

2. Specify row name

To find the last used column in row 2 , you can replace

.Cells(1, .Columns.Count) with .Cells(2, .Columns.Count)
How to use the above macro
1. Open MS Excel
2. Press ALT + F11 to open visual basic editor
3. Go to Insert >> Module to insert a module
4. Copy the above code and paste it into the module (code window)

5. Determine the last used column in a worksheet (excluding formulas displaying blank cell)
The following macro returns the last used column number in worksheet "Sheet1". It includes columns with data or a formula displaying data. It ignores cells containing formulas displaying the empty string i.e. ="".

Sub lastusedcolumn1()
Dim last As Long
last = Worksheets("Sheet1").Cells.Find(What:="*", SearchOrder:=xlColumns, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Column
MsgBox "Last used column number in sheet1 is " & last
End Sub

How to edit the above macro

Specify worksheet name
To find the last used row in worksheet "Sheet2", you can replace
Worksheets("Sheet1") with Worksheets("Sheet2")

Note : To include cells containing formulas displaying the empty string i.e. ="", you can replace LookIn:=xlValues with LookIn:=xlFormulas

About Author:
Deepanshu Bhalla

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

While I love having friends who agree, I only learn from those who don't
Let's Get Connected Email LinkedIn

Post Comment 3 Responses to "Excel VBA : Find Last Used Row and Last Used Column"

AnnaSeptember 2, 2015 at 3:29 AM
Thank you, that was very clear and helpful
KevinApril 8, 2016 at 9:13 AM
Nice. That was a great help.
Shaibul HoqueJune 23, 2020 at 1:02 PM
thank you si
please email this tutorial at
shaibulmozumder@gmail.com