Today I am going to take on one of the most frequent question people ask about Excel VBA – **how to the the last row, column or cell of a spreadsheet using VBA**. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. Therefore I will be careful to explain the differences and nuisances in our quest to find the last row, column or cell in and Excel spreadsheet.

## VBA Last Row

The Last Row may as be interpreted as:

### Last Row in a Column

To get the Last Row with data in a Column we need to use the End property of an Excel VBA Range.

Dim lastRow as Range 'Get Last Row with Data in Column Debug.Print Range("A1").End(xlDown).Row 'Result: 5 Set lastRow = Range("A1").End(xlDown).EntireRow 'Get Last Cell with Data in Row Dim lastRow as Range Set lastRow = Range("A1").End(xlDown)

### Last Row with Data in Worksheet

To get the Last Row with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastRow as Range, ws As Worksheet Set ws = ActiveSheet 'Get Last Row with Data in Worksheet using SpecialCells Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Row Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow 'Get Last Row with Data in Worksheet using Find Debug.Print Debug.Print ws.Cells.Find(What:="*", _ After:=ws.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Set lastRow = Debug.Print ws.Cells.Find(What:="*", _ After:=ws.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).EntireRow

### Last Row in Worksheet UsedRange

To get the Last Row in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Row in Worksheet UsedRange Dim lastRow as Range, ws As Worksheet Set ws = ActiveSheet Debug.Print ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row Set lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).EntireRow

## VBA Last Column

The Last Column may as be interpreted as:

**Last Column with Data in a Row****Last Column with Data in Worksheet****Last Column in Worksheet UsedRange**

### Last Column with Data in a Row

To get the Last Column with data in a Row we need to use the End property of an Excel VBA Range.

Dim lastColumn as Range 'Get Last Column with Data in Row Debug.Print Range("A1").End(xlRight).Row 'Result: 5 Set lastColumn = Range("A1").End(xlRight).EntireColumn 'Get Last Cell with Data in Column Dim lastColumn as Range Set lastColumn = Range("A1").End(xlRight)

### Last Column with Data in Worksheet

To get the Last Column with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastColumn as Range, ws As Worksheet Set ws = ActiveSheet 'Get Last Column with Data in Worksheet using SpecialCells Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Column Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn 'Get Last Column with Data in Worksheet using Find Debug.Print Debug.Print ws.Cells.Find(What:="*", _ After:=ws.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Column Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _ After:=ws.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False).EntireColumn

### Last Column in Worksheet UsedRange

To get the Last Column in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Column in Worksheet UsedRange Dim lastColumn as Range, ws As Worksheet Set ws = ActiveSheet Debug.Print ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column Set lastColumn = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).EntireColumn

## VBA Last Cell

The Last Cell may as be interpreted as:

### Last Cell in a series of data

To get the Last Cell in a series of data (table with non-blank values) we need to use the End property of an Excel VBA Range.

Dim lastCell as Range 'Get Last Cell in a series of data Dim lastCell as Range Set lastCell = Range("A1").End(xlRight).End(xlDown) Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

### Last Cells with Data in Worksheet

To get the Last Cell with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastCell as Range, ws As Worksheet Set ws = ActiveSheet 'Get Last Cell with Data in Worksheet using SpecialCells Set lastCell = ws.Cells.SpecialCells(xlCellTypeLastCell) Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column 'Get Last Cell with Data in Worksheet using Find Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _ After:=ws.Cells(1), _ Lookat:=xlPart, _ LookIn:=xlFormulas, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False) Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

### Last Cell in Worksheet UsedRange

To get the Last Cell in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Cell in Worksheet UsedRange Dim lastCell as Range, ws As Worksheet Set ws = ActiveSheet Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count,ws.UsedRange.Columns.Count) Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

## VBA UsedRange

The VBA UsedRange represents the area reserved and saved by Excel as the currently used Range on and Excel Worksheet. The **UsedRange constantly expands the moment you modify in any way a cell outside of the previously Used Range of your Worksheet.**

The UsedRange is not reduced if you Clear the Contents of Range. The only way to reduce a UsedRange is to delete the unused rows and columns.

### How to check the UsedRange

The easiest way to check the currently UsedRange in an Excel Worksheet is to select a cell (best A1) and hitting the following key combination: `CTRL`+`SHIFT`+`END`. The highlighted Range starts at the cell you selected and ends with the last cell in the current UsedRange.

### Check UsedRange in VBA

Use the code below to check the area of the UsedRange in VBA:

Dim lastCell As Range, firstCell As Range, ws As Worksheet Set ws = ActiveSheet Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count) Set firstCell = ws.UsedRange.Cells(1, 1) Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column Debug.Print "Last Cell in UsedRange. Row: " & lastCell.Row & ", Column: " & lastCell.Column

For the screen above the result will be:

First Cell in UsedRange; Row: 2, Column: 2 Last Cell in UsedRange; Row: 5, Column: 6

### First UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim firstCell as Range Set firstCell = ws.UsedRange.Cells(1, 1) Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column

### Last UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim lastCell as Range Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count) Debug.Print "Last Cell in UsedRange; Row: " & lastCell.Row & ", Column: " & lastCell.Column