The fastest way to get the last used row in a column with Excel VBA is Cells(Rows.Count, "A").End(xlUp).Row, which returns the row number of the last non-empty cell in column A. For a more robust method that ignores blank cells in the middle, use Range("A" & Rows.Count).End(xlUp).Row or the Find method.
Why You Need the Last Row
In VBA, you rarely know in advance how many rows of data a worksheet contains. Hard-coding a range like Range("A1:A100") breaks as soon as the data grows or shrinks. Getting the last row dynamically lets your macros process exactly the data that exists, avoiding empty rows, missing values, and performance slowdowns from looping over thousands of unused cells. This is a foundational skill for any data-cleaning, reporting, or automation task in Excel.
The Classic End(xlUp) Method
The most common approach mimics pressing Ctrl+Up from the bottom of the sheet. In VBA, End(xlUp) works like that keyboard shortcut: it starts from a cell and jumps up to the last non-empty cell in the same column.
To get the last row in column A, use:
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Here, Rows.Count returns the total number of rows in the sheet (1,048,576 in modern Excel). Starting from the very bottom, End(xlUp) jumps to the last cell with data. The .Row property gives its row number.
If your data is in a different column, change the column letter or index. For column B, use Cells(Rows.Count, "B") or Cells(Rows.Count, 2).
Using Find for the True Last Row
The End(xlUp) method fails if the last cell in the column is blank but there is data below it (unusual but possible) or if the column contains formulas that return empty strings. A more reliable way is to use the Find method, which searches for the last cell containing any value.
Dim lastRow As Long
Dim rng As Range
Set rng = Range("A:A").Find(What:="*", After:=Range("A1"), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not rng Is Nothing Then
lastRow = rng.Row
Else
lastRow = 0 ' No data found
End If
The What:="*" matches any text or number, SearchDirection:=xlPrevious starts from the bottom, and LookIn:=xlValues ignores formatting-only cells. This method is slower than End(xlUp) but more accurate when the column has gaps or formula artifacts.
Choosing the Right Column Reference
You can reference the column in several ways, each with its own use case:
- Column letter:
Range("A" & Rows.Count).End(xlUp).Row– easy to read, but you must change the letter for each column. - Column number:
Cells(Rows.Count, 1).End(xlUp).Row– useful in loops where the column index changes. - Named range:
Range("MyColumn").End(xlUp).Row– works if you have a named range covering the column, but be careful:Endonly works if the named range is a single column. - UsedRange:
ActiveSheet.UsedRange.Rows.Count– gives the last row of the entire sheet, not a specific column. Use only if you need the overall extent.
For most tasks, the column letter approach is clearest. If you are writing a loop that processes multiple columns, use the column index variable.
Common Pitfalls and How to Avoid Them
Several issues can trip up even experienced VBA developers:
- Empty column: If the column has no data,
End(xlUp)returns row 1, which is misleading. Check if the cell is empty first:If IsEmpty(Cells(Rows.Count, "A")) Then lastRow = 0. - Hidden rows:
End(xlUp)ignores hidden rows, so the result may skip data. UseFindwithSearchFormat:=Falseto include hidden cells, or unhide before running. - Formulas returning “”: A cell with a formula that returns an empty string is not empty to VBA.
End(xlUp)will treat it as data. To ignore such cells, useFindwithLookIn:=xlValuesandLookAt:=xlWhole, or checkLen(Cell.Value) > 0. - Merged cells: Merged cells can cause
Endto stop early. Avoid merging in data columns, or useFind.
Practical Example: Looping Through a Column
Here is a complete macro that sums the values in column A, from row 1 to the last row, and writes the total to cell C1. Save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z.
Sub SumColumnA()
Dim lastRow As Long
Dim total As Double
Dim i As Long
' Get last row in column A
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
' Sum values
For i = 1 To lastRow
total = total + Cells(i, "A").Value
Next i
' Output result
Range("C1").Value = total
End Sub
This macro assumes column A contains numeric values starting at row 1. If there are headers, start the loop at row 2. The For loop uses lastRow as the upper bound, so it automatically adapts to the data length.
Alternatives for Special Cases
Depending on your scenario, you might consider other approaches:
- UsedRange:
ActiveSheet.UsedRange.Rows.Countgives the last row of the entire sheet, not a specific column. Use only if you need the overall extent. - ListObject (Excel Table): If your data is in a formatted table, use
ListObjects("Table1").Range.Rows.CountorListObjects("Table1").DataBodyRange.Rows.Countto get the number of data rows. This is more reliable because tables track their own used range. - SpecialCells:
Range("A:A").SpecialCells(xlCellTypeLastCell).Rowreturns the last cell in the column that has any formatting or value, but it can be affected by stray formatting.
For most everyday tasks, End(xlUp) is sufficient and fastest. Use Find when you need absolute accuracy, and use table references when your data is in a structured table.
Frequently Asked Questions
How do I get the last row in column A when there are blank cells in between?
The End(xlUp) method stops at the first non-empty cell when moving upward, so if there are blanks in the middle, it still works correctly because it starts from the bottom. The only issue is if the very last cell is blank but there is data below it (which is rare). In that case, use the Find method, which scans the entire column and finds the last cell with any value, regardless of gaps.
What if my column has headers? Should I subtract 1?
No, you don’t subtract 1. The last row is the same whether or not there is a header. If you want to loop through data rows only, start your loop at row 2 (or whatever row your headers end on). For example, For i = 2 To lastRow skips the header row.
Does End(xlUp) work in Excel for Mac?
Yes, the VBA code is identical on Mac. The keyboard shortcut is Ctrl+Up on Windows and Control+Up on Mac, but the VBA method End(xlUp) behaves the same. The only difference is that Rows.Count might be different in older Mac versions, but in current versions it is the same 1,048,576.
Why does my code return row 1 when the column is empty?
Because End(xlUp) from the bottom of the sheet stops at row 1 if there is no data anywhere above. To avoid this, check if the cell at row 1 is empty before using the result. For example: If IsEmpty(Range("A1")) Then lastRow = 0 Else lastRow = Cells(Rows.Count, "A").End(xlUp).Row.
Can I use this to find the last row in a different worksheet?
Yes, just qualify the range with the worksheet. For example, to get the last row in column A of a sheet named “Data”, use Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row. Make sure the worksheet is spelled correctly and exists.