Excel VBA: Get Last Used Row (3 Ways)

VBA & macros · Updated November 22, 2026

The fastest way to get the last used row in Excel VBA is Cells(Rows.Count, 1).End(xlUp).Row, which returns the last non-empty row in column A. For the last row across the entire worksheet, use ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 or the more robust ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row. This article explains each method, when to use them, and common pitfalls.

Why “Last Used Row” Matters

In VBA, you rarely know exactly how many rows of data a worksheet contains. Hard-coding a range like “A1:A100” breaks as soon as data is added or removed. The last used row is the anchor for dynamic ranges—whether you’re copying data, applying formatting, looping through records, or building a chart. Using a fixed range is the most common cause of macro errors like “Run-time error 1004” or, worse, silently processing the wrong cells.

The Three Core Methods

Each method answers a slightly different question. Choose based on what “last used” means for your task.

  • Column-specific last row: Cells(Rows.Count, 1).End(xlUp).Row – finds the last non-empty cell in column 1 (A). Replace 1 with the column number or letter (e.g., 3 for C, or "C"). This is the most common and fastest method.
  • Entire sheet last row: ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1 – gives the last row of the rectangular area that has ever been used, including formatting. This can be larger than the actual data if you once formatted a cell far down.
  • True last cell: ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row – returns the row of the last cell that Excel considers “used.” This is similar to pressing Ctrl+End. It can be unreliable if the sheet has leftover formatting or deleted data.

How End(xlUp) Works

The End method is the VBA equivalent of pressing Ctrl+Arrow keys. Cells(Rows.Count, 1) goes to the very bottom of column A (row 1,048,576 in modern Excel). .End(xlUp) then jumps up to the last non-empty cell, just like pressing Ctrl+Up. The .Row property returns that row number.

Example: if your data occupies A1:A25, the expression returns 25. If column A is completely empty, it returns 1 (the starting cell) because End stops at the first cell it hits. This is a common edge case to handle.

' Get last row in column A (assuming data starts at A1)
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "Last row: " & lastRow

Always declare the variable as Long, not Integer, because row numbers exceed the Integer limit (32767).

Using UsedRange and SpecialCells

When you need the last row across all columns, UsedRange is a good start. However, UsedRange can include cells that were formatted but are now empty—for example, if you applied a fill color to a cell and then cleared its contents. To get the true last row of data, combine it with Find or use SpecialCells carefully.

Here’s a reliable pattern that finds the last row with data in any column:

' Find last row with data in any column
Dim lastRow As Long
lastRow = ActiveSheet.Cells.Find(What:="*", _
    After:=ActiveSheet.Range("A1"), _
    LookAt:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
If lastRow = 0 Then lastRow = 1 ' handle empty sheet

The Find method with What:="*" looks for any text or formula. SearchDirection:=xlPrevious starts from the bottom-right and moves up, so it finds the last used row. This is more robust than UsedRange because it ignores formatting-only cells.

Common Pitfalls and How to Avoid Them

  • Empty column: End(xlUp) returns 1 if the column is empty. Check for that: If lastRow = 1 And IsEmpty(Range("A1")) Then lastRow = 0.
  • Hidden rows: End(xlUp) does not skip hidden rows—it treats them as normal cells. If you have filtered data, the last visible row may differ. Use AutoFilter or loop through visible cells instead.
  • Formulas returning empty strings: A cell with ="" is not empty; End(xlUp) will count it. If you want to ignore those, use Find with LookIn:=xlValues and a more complex loop.
  • UsedRange stale: After deleting rows, UsedRange may still reference old cells. Press Ctrl+End to see Excel’s idea of the last cell. In VBA, you can reset it with ActiveSheet.UsedRange (accessing it refreshes it).
  • Multiple sheets: Always qualify with a worksheet object, e.g., Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row. Using ActiveSheet can cause errors if the user switches sheets.

Choosing the Right Method for Your Task

Here’s a quick guide:

  • If you know the data is in a specific column (e.g., column A has IDs): use End(xlUp)—it’s fast and simple.
  • If you need the last row of a contiguous block of data that spans multiple columns: use UsedRange or Find.
  • If you’re building a dynamic named range or chart source: use Find to avoid including stray formatting.
  • If you’re looping through rows in a table: consider using ListObject (Excel Table) instead—ListObjects(1).Range.Rows.Count gives the row count, and .DataBodyRange gives only data rows.

Frequently Asked Questions

Why does my macro return row 1048576?
That happens when End(xlUp) is used on a completely empty column. The starting cell is the very bottom, and since there’s no data above, it stays there. Check if the column is empty before using the result.

How do I get the last row in a specific column like B or D?
Replace the column index: Cells(Rows.Count, 2).End(xlUp).Row for column B, Cells(Rows.Count, 4).End(xlUp).Row for D. You can also use a letter string: Range("B" & Rows.Count).End(xlUp).Row.

Does End(xlUp) work with filtered data?
It works on the underlying data, not just visible rows. If you filter and then run End(xlUp), it will still see hidden rows. To get the last visible row, you need to loop through visible cells or use SpecialCells(xlCellTypeVisible).

What is the difference between UsedRange and SpecialCells(xlCellTypeLastCell)?
UsedRange is the rectangular area that has ever been used, including formatting. SpecialCells(xlCellTypeLastCell) returns the last cell Excel thinks is used, which is often the same but can be larger if there’s leftover formatting. Both can be reset by clearing the extra cells or saving the file.

How do I get the last row of a table (ListObject)?
If your data is in an Excel Table (Insert > Table), use ListObjects(1).DataBodyRange.Rows.Count for the number of data rows, or ListObjects(1).Range.Rows.Count for total rows including the header. This is more reliable than any manual method.

My UsedRange returns a row too high because of formatting. How do I fix it?
Delete the extra rows or clear all formatting from unused cells. In VBA, you can force a reset with ActiveSheet.UsedRange (just reading it often updates it). If that fails, use the Find method to get the true last data row.

Is there a way to get the last used row in Google Sheets?
Yes, but the syntax is different. In Google Sheets, you’d use getLastRow() in Apps Script, not VBA. For example: sheet.getLastRow() returns the last row with content. VBA is only for Excel.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.