Excel VBA : Find Last Used Row and Last Used Column

Best Online Course : SAS Programming with 50+ Case Studies

- Explain Programming Concepts in Simple English
- Live Projects & Case Studies
- Job Placement Assistance
- Get 20% off till July 14, 2017

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

Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has close to 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like retail and commercial banking, Telecom, HR and Automotive.


While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

2 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

Next → ← Prev