Excel VBA Get Last Row in Column: 4 Ways

VBA & macros · Updated November 22, 2026

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: End only 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. Use Find with SearchFormat:=False to 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, use Find with LookIn:=xlValues and LookAt:=xlWhole, or check Len(Cell.Value) > 0.
  • Merged cells: Merged cells can cause End to stop early. Avoid merging in data columns, or use Find.

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.Count gives 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.Count or ListObjects("Table1").DataBodyRange.Rows.Count to get the number of data rows. This is more reliable because tables track their own used range.
  • SpecialCells: Range("A:A").SpecialCells(xlCellTypeLastCell).Row returns 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.

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.