The fastest way to get the last row with data in a specific column using Excel VBA is Cells(Rows.Count, "A").End(xlUp).Row for column A, or Range("A" & Rows.Count).End(xlUp).Row. This returns the row number of the last non-empty cell in that column, working from the bottom upward.
Why the End(xlUp) Method Works
In Excel, the End property mimics pressing Ctrl+Arrow keys on the keyboard. End(xlUp) from the very bottom cell of a column jumps up to the last non-empty cell, stopping at the first blank cell it encounters. Because Excel’s worksheet has 1,048,576 rows, starting from row 1,048,576 and going up ensures you find the true last used row in that column, even if there are gaps in the data.
This method is fast, reliable, and works in almost every version of Excel, including Excel for Microsoft 365, Excel 2021, 2019, and older versions. It does not require any loops or special functions, making it the go-to approach for most VBA tasks.
Basic Code Examples for Different Columns
Here are the most common patterns you’ll use. Each assumes you are working with the active sheet unless you specify a worksheet object.
- Column A:
lastRow = Cells(Rows.Count, "A").End(xlUp).Row - Column C:
lastRow = Cells(Rows.Count, "C").End(xlUp).Row - Column by number (e.g., column 5 = E):
lastRow = Cells(Rows.Count, 5).End(xlUp).Row - Using Range syntax:
lastRow = Range("A" & Rows.Count).End(xlUp).Row
All of these return a Long integer. If the column is completely empty, they return row 1, because the starting point (row 1,048,576) is empty and End(xlUp) stops at the first non-empty cell, which is none, so it goes to row 1. Be aware of this edge case.
How to Use the Result in Your Code
Once you have the last row, you can use it to define ranges, loop through data, or copy values. Here’s a complete macro example that finds the last row in column A and then writes a message:
Sub FindLastRow()
Dim ws As Worksheet
Dim lastRow As Long
' Set the worksheet you want to work with
Set ws = ThisWorkbook.Sheets("Sheet1")
' Find last row with data in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Show the result
MsgBox "Last row in column A is: " & lastRow
End Sub
Remember to save a copy of your workbook before running any macro, as macros cannot be undone with Ctrl+Z. You can also use the result to loop through rows, for example:
For i = 1 To lastRow
' Process each row
Next i
Alternative Methods: UsedRange and SpecialCells
While End(xlUp) is the most common, you might encounter other approaches. Each has trade-offs.
- UsedRange:
ws.UsedRange.Rows.Countgives the number of rows in the used range, but it includes any formatting or blank rows that have been touched, so it can overestimate the last row. Use with caution. - SpecialCells(xlCellTypeLastCell):
ws.Cells.SpecialCells(xlCellTypeLastCell).Rowreturns the last row of the entire used range, but it can be unreliable if the sheet has been modified and not recalculated. It also includes formatting. - Find method:
ws.Columns("A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Rowis more robust because it finds the last cell with any content (including formulas that return empty strings), but it is slower and requires error handling if the column is empty.
For most simple tasks, End(xlUp) is sufficient. If you need to ignore cells that contain only spaces or formulas returning empty strings, the Find method is better.
Handling Empty Columns and Gaps in Data
A common misconception is that End(xlUp) finds the last row of a contiguous block of data. Actually, it finds the last non-empty cell from the bottom, regardless of gaps. For example, if column A has data in rows 1, 2, 3, 5, and 7, End(xlUp) returns 7, which is correct.
However, if the column is completely empty, the result is 1. To avoid errors, you can add a check:
If ws.Cells(ws.Rows.Count, "A").End(xlUp).Row = 1 And ws.Cells(1, "A").Value = "" Then
MsgBox "Column A is empty"
Else
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
End If
This checks whether the result is row 1 and whether that cell is truly empty. If both are true, the column has no data.
Working with Different Worksheets and Workbooks
Always qualify your range with the worksheet to avoid referencing the wrong sheet. If you omit the worksheet, VBA uses the active sheet, which may not be the one you intend. For example:
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
If you need to reference another workbook, use Workbooks("Book2.xlsx").Sheets("Sheet1") instead of ThisWorkbook. This is especially important when running macros from a personal macro workbook or an add-in.
Performance Considerations and Best Practices
Using End(xlUp) is extremely fast, even on large datasets, because it leverages Excel’s internal navigation. To make your code even more efficient, avoid selecting or activating cells. Instead of Range("A1").Select, directly assign values or use variables. Also, consider turning off screen updating while the macro runs:
Application.ScreenUpdating = False
' ... your code ...
Application.ScreenUpdating = True
This prevents flickering and speeds up execution. Remember to reset it even if an error occurs, so use error handling like On Error GoTo to ensure it gets turned back on.
Frequently Asked Questions
What does End(xlUp) actually do?
It simulates pressing Ctrl+Up Arrow from a given cell. When you start from the bottom of a column, it jumps up to the last non-empty cell, skipping any blanks below it. That’s why it’s perfect for finding the last row with data.
How do I get the last row from a specific cell, not the whole column?
You can use Range("A5").End(xlUp).Row to find the last non-empty cell above row 5 in column A. This is useful if you want to find the last row of a block that starts below a header or a separator.
Why does my code return row 1 when the column is empty?
Because End(xlUp) starts at the bottom and goes up until it finds a non-empty cell. If there are none, it stops at the very top row, which is row 1. You need to add a check for an empty column, as shown earlier.
Can I use this method to find the last column with data?
Yes, the equivalent for columns is Cells(1, Columns.Count).End(xlToLeft).Column. This starts from the last column (XFD) and moves left to find the last column with data in row 1. Adjust the row number as needed.
Does this work in Excel for Mac?
Yes, the VBA code is identical on Mac. The only difference is that keyboard shortcuts use Cmd instead of Ctrl, but the End property works the same. Excel for the web does not support VBA, so you would need to use Office Scripts or Google Apps Script instead.
What is the difference between Rows.Count and 1048576?
Rows.Count returns the total number of rows in the worksheet, which is 1,048,576 in modern Excel. Using Rows.Count is safer because it adapts if you ever use an older file format or a different version, though the number is the same for all current versions.
Related
- Excel Staff Schedule Template: Free Options & How to Build One
- Excel Spreadsheet Templates for Tracking Time
- Excel Spreadsheet Timesheet Template: How to Make One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Find the First Empty Cell in a ColumnTo find the first empty cell in an Excel column with VBA, start at the first row you…
- Excel VBA: How to Enable AutoFilterTo enable AutoFilter with VBA, select the worksheet range that includes the column headers and run its AutoFilter…
- Named Range in VBA: Create, Read, and Manage Excel NamesIn Excel VBA, a named range is a defined name that refers to a cell, a range of…
- Excel VBA Offset: How to Reference Nearby CellsIn Excel VBA, Offset refers to a cell or range a chosen number of rows and columns away…
- Excel VBA Macro Functions: What They Are & How to UseExcel VBA macro functions are custom procedures written in Visual Basic for Applications (VBA) that you can call…
- VBA MsgBox Font Size: What You Can and Cannot ChangeYou cannot set the font size of VBA’s built-in MsgBox in Excel: the function has no font-size argument…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.