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
Longinstead ofInteger. - 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).Rowstill works because it starts from the bottom, but if you useRange("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
FindwithxlPreviouson that column. - You need the last row across multiple columns – Use
Findon the entire sheet or theUsedRangeapproach. - You’re working with a table (ListObject) – Use
ListObjects("Table1").Range.Rows.Countto 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
- Excel Template for Family Budget: How to Build One
- Excel Template for HOA Budget: How to Build One
- Excel Template for Event Budget: How to Build One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Sort Custom Order: How to Set It UpTo sort rows in a custom sequence with Excel VBA, add a sort field with the CustomOrder argument,…
- VBA Array Length: Count Elements and Find BoundsIn VBA, calculate an array’s length as UBound(array) - LBound(array) + 1. The expression works for a one-dimensional…
- Excel VBA Cell Interior Color: Read, Set, and ClearIn Excel VBA, a cell’s fill color is controlled through its Interior object: set it with Range("A1").Interior.Color =…
- Excel VBA Copy and Paste a Range to Another WorkbookTo copy a range from one open Excel workbook to another with VBA, use sourceRange.Copy Destination:=targetSheet.Range("B2"), replacing the…
- Excel VBA Protect Sheet Allow Edit ObjectsYes, you can use Excel VBA to protect a worksheet while still allowing users to edit objects such…
- Excel VBA Month Function: Syntax, Examples, and TipsIn Excel VBA, the Month function returns the month number from a date: an integer from 1 for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.