# How to Find the Last Used Row and Column in Excel VBA?

We use Range.SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet.

**Sample Data:**

**Syntax:**

expression.SpecialCells (Type, Value)

Eg: To return the last used cell address in an activesheet. ActiveSheet.Range(“A1”).SpecialCells(xlCellTypeLastCell).Address

**VBA Code:**

Declaring Variables:

Variable | Data Type | Comments |
---|---|---|

LastRow | Long | Find and store last used row |

LastCol | Long | store last used column |

LastCell | String | store last used cell address |

'Variable Declaration Dim LastRow As Long, LastCol As Long, LastCell As String

Use SpecialCells function to find last used row/column/cell

'Find Last Used Row LastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row 'Find Last Used Column LastCol = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column 'Find Last Used Cell LastCell = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address

Concatenate all three variables (LastRow/LastCol/LastCell), add a new line between variables use Chr(10). Show the final output in an Excel Message box.

'Display the last used row/column/cell MsgBox "Last Used Row : " & LastRow & Chr(10) & "Last Used Column : " & LastCol & Chr(10) & "Last Used Cell : " & LastCell

### Implementation:

Follow the below steps to find the Last Used Row and Last Used Column in Excel VBA

**Step 1: **Add a shape (**Find Last Row/Column/Cell**) on your worksheet.

**Step 2: **Right-click on “**Find Last Row/Column/Cell**” and “Assign Macro..”

**Step 3: **Select “**findLastUsedCell**”, you can see a list of macros if available in your workbook

**Step 4: **Save your excel file as “Excel Macro-Enabled Workbook” *.xlsm

**Step 5:** Click “**Find Last Row/Column/Cell**” to execute the VBA code. Code will popup below output for the above given example (Output).

**Output:**