Excel VBA Last Non Empty Row: Find It Fast

VBA & macros · Updated November 22, 2026

The fastest way to find the last non-empty row in a column with Excel VBA is Cells(Rows.Count, 1).End(xlUp).Row — this returns the row number of the last cell with data in column A. For the entire worksheet, use Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row. Both methods are reliable for most datasets, but each has caveats covered below.

Why “Last Row” Is Trickier Than It Looks

Excel tracks the “used range” differently than you might expect. If you delete values from cells, the formatting or a stray space can remain, making Excel think the row is still “used.” Similarly, formulas that return an empty string ("") count as data for the End(xlUp) method. So the last non-empty row often isn’t the same as the last row with visible content.

This matters because macros that loop through rows or copy ranges will process phantom rows, causing errors or wasted time. Knowing which method to use depends on what you mean by “non-empty”: visible values, any value including formulas, or only cells that contain something.

The Classic End(xlUp) Method

The most common approach mimics pressing Ctrl+Up from the bottom of a column. In VBA, this translates to:

Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

This assumes your data is in column A. Replace 1 with the column number (e.g., 2 for column B) or use a letter string with Range("A" & Rows.Count).End(xlUp).Row. It works by starting at the very last row of the worksheet (row 1,048,576 in modern Excel) and jumping up to the first non-empty cell.

This method is fast and perfect for a single column where data is contiguous (no blank rows in the middle). But it fails if your column has gaps: it will stop at the first blank cell above the last non-empty one, giving you a row that’s too low. For example, if A1:A10 have data, A11:A15 are blank, and A16 has a value, End(xlUp) returns 16, which is correct. But if A10 is blank and A11 has data, it returns 11 — which is still the last non-empty, so that’s fine. The real problem is when you have blanks in the middle: A1:A5 data, A6 blank, A7:A9 data. Starting from the bottom, it jumps to row 9, which is correct. Actually, End(xlUp) always returns the last non-empty cell in the column, regardless of blanks above it, because it starts from the very bottom. The issue is when you use End(xlUp) from a specific cell, like Range("A1").End(xlDown) to find the last row — that stops at the first blank. So the classic method is reliable for finding the last non-empty row in a single column.

Using Find to Cover the Whole Sheet

If you need the last row with any data across the entire worksheet, Find is more robust. This code finds the last cell with any content (including formulas that return empty strings) by searching backwards:

Dim lastRow As Long
With Sheets("Sheet1").Cells
lastRow = .Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
End With

This works because "*" matches any string of characters, and searching by rows in the previous direction starts from the bottom-right and moves up, so the first match is the last non-empty row. It ignores cells with only formatting. If the sheet is completely empty, Find returns Nothing, so you need error handling:

Dim lastRow As Long
Dim rng As Range
Set rng = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If rng Is Nothing Then
lastRow = 1 ' or 0, depending on your needs
Else
lastRow = rng.Row
End If

This method is slower than End(xlUp) but safer for messy sheets with multiple columns and hidden data.

Choosing the Right Method for Your Data

Here’s a quick guide to decide which approach fits your scenario:

  • Single column, no blanks in the data block: Use End(xlUp) — it’s fast and simple.
  • Single column with blanks in the middle: End(xlUp) still works because it starts from the bottom, not from the top. But if you need the last row of a contiguous block from the top, use Range("A1").End(xlDown).Row — that gives the first blank row, not the last non-empty.
  • Entire worksheet: Use Find to get the true last row across all columns.
  • Specific column with formulas that return empty strings: End(xlUp) counts those as non-empty. If you want to ignore them, you might need to loop or use SpecialCells.

Common Pitfalls and How to Avoid Them

One frequent mistake is assuming the last row of column A is the last row of the entire dataset. If your data extends to column Z but column A has fewer entries, Cells(Rows.Count, 1).End(xlUp).Row returns a smaller number than the true last row. Always consider which column (or the whole sheet) you actually need.

Another pitfall is using UsedRange. ActiveSheet.UsedRange.Rows.Count often returns a row that’s too large because of formatting remnants. For example, if you apply bold to row 1000 but only have data in row 10, UsedRange might report 1000. Avoid this unless you’ve cleared formatting.

Also, remember that End(xlUp) works on the active sheet unless you qualify it. Always specify the worksheet, like Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row, to avoid errors when the wrong sheet is active.

Performance and Large Datasets

For worksheets with hundreds of thousands of rows, End(xlUp) is nearly instantaneous because it uses Excel’s internal navigation. Find is also fast, but it scans the entire used range, which can be slower if the used range is bloated. If you’re working with massive datasets, consider limiting the search to a specific column or range.

If you need to find the last row in a filtered list, End(xlUp) will see hidden rows as non-empty. To find the last visible row, you might need to loop through SpecialCells(xlCellTypeVisible) — that’s more advanced and rarely needed.

Alternatives: SpecialCells and Looping

For very specific needs, you can use SpecialCells to find the last cell with a constant or formula:

Dim lastRow As Long
On Error Resume Next
lastRow = Cells.SpecialCells(xlCellTypeConstants).Row
' or xlCellTypeFormulas
On Error GoTo 0

This returns the row of the last constant cell, but it fails if there are no constants. Looping is the slowest but most flexible method, useful when you need to check each cell’s value:

Dim lastRow As Long
Dim i As Long
For i = Rows.Count To 1 Step -1
If Cells(i, 1).Value <> "" Then
lastRow = i
Exit For
End If
Next i

This is only recommended for small ranges because it’s slow on large sheets.

Frequently Asked Questions

Why does my macro return row 1048576 even though my data ends at row 100?
That happens when you use End(xlUp) on an empty column. If the column is completely blank, it returns the very last row of the sheet. Always check for empty columns before assuming the result is valid.

How do I find the last non-empty row in a specific column, not column A?
Change the column index: Cells(Rows.Count, 3).End(xlUp).Row for column C. Or use a letter: Range("C" & Rows.Count).End(xlUp).Row.

Does End(xlUp) work on a filtered list?
Yes, but it includes hidden rows. If you filter and then run End(xlUp) from the bottom, it will stop at the last non-empty cell even if it’s hidden. To get the last visible row, you need to loop through visible cells.

What if my data has formulas that return empty strings?
Those cells are considered non-empty because they contain formulas. If you want to ignore them, you can use SpecialCells(xlCellTypeConstants) or loop and check Len(Cells(i,1).Value) > 0.

Can I use UsedRange to find the last row?
You can, but it’s unreliable because it includes formatting. If you’ve formatted cells beyond your data, UsedRange will report a larger row. It’s better to use Find or End(xlUp).

Is there a way to find the last row without using VBA?
Yes, you can press Ctrl+End to jump to the last used cell, or use the formula =SUMPRODUCT(MAX((A:A<>"")*ROW(A:A))) in a cell to get the last non-empty row number in column A. That formula works in Excel 365 and earlier versions.

Always save a copy of your workbook before running any macro, because VBA actions cannot be undone with Ctrl+Z.

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.