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). Replace1with the column number or letter (e.g.,3for 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. UseAutoFilteror 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, useFindwithLookIn:=xlValuesand a more complex loop. - UsedRange stale: After deleting rows,
UsedRangemay still reference old cells. Press Ctrl+End to see Excel’s idea of the last cell. In VBA, you can reset it withActiveSheet.UsedRange(accessing it refreshes it). - Multiple sheets: Always qualify with a worksheet object, e.g.,
Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row. UsingActiveSheetcan 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
UsedRangeorFind. - If you’re building a dynamic named range or chart source: use
Findto avoid including stray formatting. - If you’re looping through rows in a table: consider using
ListObject(Excel Table) instead—ListObjects(1).Range.Rows.Countgives the row count, and.DataBodyRangegives 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
- Excel Truck Group Jobs: Pay, Hiring & Reviews
- Excel Track Purdue Global: What It Is & How It Works
- Excel Transpose Table: Switch Rows and Columns
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Determine a Cell’s Background Color ValueIn Excel VBA, read a cell’s fill color with Range.Interior.Color; for example, Worksheets("Sheet1").Range("B2").Interior.Color returns its color as a…
- VBA Pivot Table Delete: What to Remove and HowYes. In Excel for Microsoft 365 on Windows, remove a PivotTable report with VBA by clearing its full…
- Excel VBA Freeze Panes at a Specific CellYes. Activate the worksheet, select the cell that marks the top-left corner of the scrolling area, then set…
- Macros Disabled in Excel: Fix & Enable GuideYes, the message “Because of your security settings, macros have been disabled” means Excel’s Trust Center has blocked…
- Excel VBA Date: Get, Store, and Work With DatesIn Excel VBA, Date returns the current date from the computer’s system clock, without a time component; assign…
- Excel VBA: How to Include Double Quotes in a StringIn Excel VBA, write two double-quote characters inside a string literal wherever you want one actual double quote.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.