Excel VBA : Find Last Used Row and Last Used Column

Deepanshu Bhalla 3 Comments ,
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")

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)


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")

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)

Note :  To include cells containing formulas displaying the empty string i.e. ="", you can replace LookIn:=xlValues with LookIn:=xlFormulas
Related Posts
Spread the Word!
Share
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.

3 Responses to "Excel VBA : Find Last Used Row and Last Used Column"
  1. Thank you, that was very clear and helpful

    ReplyDelete
  2. Nice. That was a great help.

    ReplyDelete
  3. thank you si
    please email this tutorial at
    shaibulmozumder@gmail.com

    ReplyDelete
Next → ← Prev