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

The following macro returns the last used row number in column A

1. Specify worksheet name

To find the last used row in a column in worksheet "Sheet2", you can replace

2. Specify column name

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)

The following macro returns the last used row number in worksheet "Sheet1". It includes rows with data or a formula displaying data.

Specify worksheet name

To find the last used row in worksheet "Sheet2", you can replace

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)

The following macro returns the last used row number in worksheet "Sheet1". It includes rows with data or a formula displaying

Specify worksheet name

To find the last used row in worksheet "Sheet2", you can replace

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)

The following macro returns the last used column number in row 1

1. Specify worksheet name

To find the last used column in a row in worksheet "Sheet2", you can replace

2. Specify row name

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)

The following macro returns the last used column number in worksheet "Sheet1". It includes columns with data or a formula displaying data.

Specify worksheet name

To find the last used row in worksheet "Sheet2", you can replace

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)

**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**

Thank you, that was very clear and helpful

ReplyDeleteNice. That was a great help.

ReplyDelete