Excel VBA: Get Last Row with Data (3 Ways)

VBA & macros · Updated November 22, 2026

The quickest way to get the last row with data in Excel VBA is Cells(Rows.Count, 1).End(xlUp).Row, which returns the row number of the last non-empty cell in column A. For a specific column, replace the 1 with that column’s number (e.g., 2 for column B). This method is fast, reliable, and works in nearly every version of Excel, including Excel for Microsoft 365, Excel 2021, and older versions.

Why You Need the Last Row in VBA

When writing macros that process data, you almost always need to know where your data ends. Looping through a fixed range like Range("A1:A1000") is wasteful and breaks if you have more than 1000 rows. Using the last row lets your code adapt to any dataset size, so it works on a 10-row list today and a 10,000-row list tomorrow without changes. It also prevents errors like trying to read empty cells or overwriting data that sits below your intended range.

How the End(xlUp) Method Works

The End method is the VBA equivalent of pressing Ctrl+Up Arrow in Excel. Starting from a cell, it jumps to the last non-empty cell in that direction before hitting a blank cell. The syntax is:

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

Here, Rows.Count returns the total number of rows in the worksheet (1,048,576 in modern Excel). So Cells(Rows.Count, 1) refers to the very last cell in column A. Then End(xlUp) moves upward until it finds the last non-empty cell. The .Row property extracts that row number.

This works perfectly when there are no blank rows in the middle of your data. If your data has gaps, End(xlUp) will stop at the first blank row it encounters, giving you a row that is not truly the last. In that case, you need a different approach (see below).

Alternative: Using Find to Get the True Last Row

If your data contains blank cells in the middle, use the Find method to locate the last cell with any value in a column. This searches the entire column, ignoring blanks, and returns the last non-empty cell regardless of gaps.

Dim lastRow As Long
Dim rng As Range
Set rng = Columns("A").Find(What:="*", After:=Range("A1"), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not rng Is Nothing Then
lastRow = rng.Row
Else
lastRow = 1 ' no data found
End If

This code searches column A for any value ("*" is a wildcard for any text or number), starting from A1 and moving backward. The xlPrevious direction makes it find the last match. If no data exists, rng is Nothing, and you handle that case.

This method is slightly slower than End(xlUp) but is more robust. Use it when your data has blank rows or when you want to be absolutely certain you have the last row.

Getting the Last Row Across the Entire Used Range

Sometimes you need the last row that has data in any column, not just a specific one. For example, if your data is in columns A through D but column C has values in row 500 while column A stops at row 300, the true last row is 500. The UsedRange property can help:

Dim lastRow As Long
lastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

This calculates the bottom row of the used range. However, UsedRange can sometimes include formatting-only cells, giving a larger row than expected. A more reliable way is to use Find across the entire sheet:

Dim lastRow As Long
Dim rng As Range
Set rng = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not rng Is Nothing Then
lastRow = rng.Row
Else
lastRow = 1
End If

This searches every cell in the worksheet, not just one column, and returns the row of the last cell with a value.

Common Mistakes and How to Avoid Them

  • Using End(xlDown) from A1 – This stops at the first blank cell, which is rarely the last row. Avoid it.
  • Not declaring the variable as Long – Row numbers can exceed 32,767, so use Long instead of Integer.
  • Assuming data starts in row 1 – If your data starts in row 5, adjust the starting point accordingly. For example, Cells(Rows.Count, 1).End(xlUp).Row still works because it starts from the bottom, but if you use Range("A1").End(xlDown), you’ll get the wrong result.
  • Forgetting to handle empty sheets – If the column is completely empty, End(xlUp) from the bottom returns row 1, which might be wrong. Check for that case.

Choosing the Right Method for Your Data

Here’s a quick guide:

  • Data has no blank rows in the column – Use End(xlUp). It’s fast and simple.
  • Data has blank rows scattered – Use Find with xlPrevious on that column.
  • You need the last row across multiple columns – Use Find on the entire sheet or the UsedRange approach.
  • You’re working with a table (ListObject) – Use ListObjects("Table1").Range.Rows.Count to get the number of rows, then add the header row offset.

Frequently Asked Questions

Does End(xlUp) work if my data has formulas that return empty strings?

Yes, but with a caveat. If a formula like =IF(A1="","",B1) returns "" (empty text), End(xlUp) considers that cell as having a value because it contains a formula. To ignore such cells, you’d need to use Find with LookIn:=xlValues and also check for Len(Trim(cell.Value)) > 0 in a loop.

How do I get the last row in a specific sheet, not the active sheet?

Reference the sheet directly, for example: Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row. This avoids errors if the user is on a different sheet.

What if my data is in a row instead of a column?

Use End(xlToLeft) from the last column. For example, to get the last column with data in row 1: Cells(1, Columns.Count).End(xlToLeft).Column. To get the last row in a column, you’re already doing the right thing.

Is there a way to get the last row without using VBA?

Yes, you can use the formula =COUNTA(A:A) to count non-empty cells, but that doesn’t give the row number if there are blanks. A more robust formula is =SUMPRODUCT(MAX((A:A<>"")*ROW(A:A))) entered as an array formula (Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365). This returns the last row with data in column A.

Can I use UsedRange to get the last row reliably?

It can be unreliable because Excel tracks formatting. If you’ve ever formatted cells far below your data, UsedRange will include those rows. To reset it, you can press Ctrl+End to see where Excel thinks the last cell is, and then delete extra rows/columns. In VBA, it’s safer to use Find or End(xlUp).

Practical Example: Looping Through Data

Here’s a complete macro that uses the last row to loop through a list and highlight cells with a value greater than 100:

Sub HighlightLargeValues()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long

' Save a copy of your workbook before running this macro
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

For i = 1 To lastRow
If ws.Cells(i, "A").Value > 100 Then
ws.Cells(i, "A").Interior.Color = RGB(255, 255, 0)
End If
Next i
End Sub

This macro assumes data starts in row 1 and is in column A. It loops from row 1 to the last row and highlights cells with values over 100 in yellow.

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.