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, useRange("A1").End(xlDown).Row— that gives the first blank row, not the last non-empty. - Entire worksheet: Use
Findto 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 useSpecialCells.
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
- Excel to Notepad Converter Online: How to Convert
- Excel Timesheet Template for Multiple Projects
- Excel Templates for Time Tracking: Best Free Options
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Loop Only Through Visible Rows?Yes. In Excel VBA, loop through the rows in your data range and check whether each row is…
- Excel VBA: Copy an Array to a Worksheet Range EasilyTo copy a two-dimensional VBA array to cells, assign it to a worksheet range whose size matches the…
- Excel VBA Find Character in a String: InStr ExamplesIn Excel VBA, use the InStr function to find a character or a longer piece of text inside…
- Excel VBA: Loop Through All Files in a FolderTo loop through files in a folder with Excel VBA, use the Dir function to get the first…
- Excel VBA Out of Memory Error: Causes and FixesAn Excel VBA “Out of memory” error usually means Excel cannot allocate enough usable memory for the macro’s…
- Macro Recorder Best Options for Excel and Windows PCsThe best macro recorder depends on what you want to automate: for repeated tasks inside Excel, start with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.