The fastest way to move to the last row with data in Excel VBA is Range("A" & Rows.Count).End(xlUp).Select (or .Offset(1).Select to go one past it). This mimics pressing Ctrl+Up from the bottom of a column; for the last used cell in the entire sheet, use Cells.SpecialCells(xlCellTypeLastCell).Select.
Why You Need This in VBA
When writing macros that append data, delete rows, or format tables, you almost always need to know where the data ends. Hard-coding row numbers (like Range("A100")) breaks as soon as the sheet changes. The End(xlUp) method is the VBA equivalent of pressing Ctrl+Up on the keyboard—it jumps from a starting cell to the last non-empty cell in that column. It is fast, reliable, and works in every version of Excel from 2007 onward.
How End(xlUp) Works
The End method takes a direction constant: xlUp, xlDown, xlToLeft, or xlToRight. It starts from a cell and moves in that direction until it hits the edge of a contiguous block of data or a blank cell. For finding the last row, you start from the very bottom of the sheet (Rows.Count returns 1,048,576 in modern Excel) and go up. That way, you always start past any data and stop at the last used cell in that column.
Example: to select the last non-empty cell in column A:
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(lastRow, "A").Select
If column A is completely empty, this returns row 1, because End(xlUp) stops at the starting cell if no data is found. To find the last row across the entire sheet, use Cells.SpecialCells(xlCellTypeLastCell).Row, but note that this may include formatting-only cells.
Choosing the Right Column
Which column you use matters. If your data has gaps in column A but is solid in column B, using column A will give the wrong last row. A common practice is to use a column that is always filled for every record, like an ID column. If your data is a proper Excel Table (ListObject), you can use ListObjects("Table1").Range.Rows.Count instead—that counts only the rows in the table, ignoring blanks.
For example, if your table starts at A1 and column B is the ID column, use:
Dim lastRow As Long
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
If you need the row after the last data (to append a new record), add 1: lastRow + 1. But be careful—if the column has blanks in the middle, End(xlUp) will stop at the first blank it encounters from the bottom, not the true last row. For a reliable last row even with gaps, loop from the bottom up:
Dim r As Long
For r = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Cells(r, "A").Value <> "" Then
lastRow = r
Exit For
End If
Next r
Common Problems and Pitfalls
The most frequent issue is using End(xlUp) on a column that has blank cells in the middle. For example, if column A has data in rows 1-5, blank in 6, data in 7-10, End(xlUp) from the bottom returns row 7, not 10. That is because it stops at the first blank cell when moving up. To avoid this, either use a column with no blanks, or use the loop above.
Another pitfall is relying on UsedRange or SpecialCells(xlCellTypeLastCell). These can include cells that were formatted but are now empty, giving you a row that is too high. To reset the used range, press Ctrl+End in Excel—if it goes to a cell far from your data, save and reopen the file, or use ActiveSheet.UsedRange carefully.
Also, remember that End(xlUp) works on the active sheet unless you qualify it. Always specify the worksheet, like Worksheets("Data").Cells(Rows.Count, "A").End(xlUp).Row, to avoid errors when the wrong sheet is active.
Alternatives: UsedRange and Find
For a quick one-liner, ActiveSheet.UsedRange.Rows.Count gives the number of rows in the used range, but that includes blank rows at the top if the data starts lower. To get the last row number, use ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1. This is often sufficient, but it can be thrown off by stray formatting.
The Find method is more robust for finding the last cell with a specific value or format:
Dim lastCell As Range
Set lastCell = Cells.Find(What:="*", After:=Range("A1"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
If Not lastCell Is Nothing Then
lastRow = lastCell.Row
End If
This finds the last cell with any value in the sheet, which is often the true last row of data. It is slower than End but more accurate for sheets with gaps.
Writing a Macro to Go to Last Row
Here is a complete macro that moves the active cell to the last row of data in column A, and then to the first empty cell below it:
Sub GoToLastRow()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ActiveSheet
' Find last non-empty cell in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Select that cell, or the one below if you want to append
ws.Cells(lastRow, "A").Select
' To go to the next empty cell, use: ws.Cells(lastRow + 1, "A").Select
End Sub
Before running any macro, save a copy of your workbook—macros cannot be undone with Ctrl+Z. To run it, press Alt+F8, select GoToLastRow, and click Run.
Performance and Best Practices
Using End(xlUp) is extremely fast because it is a single operation, not a loop. For large datasets (hundreds of thousands of rows), it is the preferred method. Avoid looping through every cell to find the last row—that can be slow. Also, avoid using Select in your code unless you need the user to see the selection; instead, store the row number in a variable and use it directly. For example, to append data to the next row:
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row + 1
ws.Cells(lastRow, "A").Value = "New Data"
This writes directly without selecting. If you are working with an Excel Table, use ListObjects("Table1").ListRows.Add to add a row—it handles the last row automatically.
Frequently Asked Questions
Q: How do I find the last row in a specific column, not column A?
Replace the column letter in the code. For column C, use Cells(Rows.Count, "C").End(xlUp).Row. You can also use a column number: Cells(Rows.Count, 3) for column C.
Q: What if my data has blank rows in the middle?
Use the loop method shown earlier, or use Find with SearchDirection:=xlPrevious to get the last non-empty cell in the entire sheet, which handles gaps.
Q: How do I move to the last row and then enter data?
After finding lastRow, use Cells(lastRow + 1, "A").Value = "text". That writes to the first empty row below the data.
Q: Does this work in Excel for Mac?
Yes, the VBA code is identical. The only difference is keyboard shortcuts—on Mac, Ctrl+Up is Control+Up (or Command+Up in some contexts).
Q: Why does my macro go to row 1048576 even when the sheet is empty?
Because Rows.Count returns 1,048,576, and if the column is empty, End(xlUp) stops at the starting cell. To handle empty sheets, add an If check: if lastRow = 1 and cell A1 is empty, treat it as no data.
Related
- Excel Template to Pay Off Debt? Yes, Here’s How
- Excel Inventory Template: How to Track Stock
- Excel Template Job Schedule: 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
- Macro Recorder for Mac Excel: Setup, Steps, and LimitsYes. Excel for Microsoft 365 for Mac has a macro recorder: enable the Developer tab if it is…
- Excel VBA: Convert a Variant to a StringIn Excel VBA, use CStr to convert a Variant value to a String: for example, textValue = CStr(value).…
- Can Google Sheets Run Excel Macros? Key DifferencesNo. Google Sheets cannot run Excel VBA macros. It has its own automation tools—Google Apps Script and a…
- Excel VBA Bubble Sort: Code, Steps, and AlternativesExcel VBA can sort a selected, single-column range of numbers with a bubble-sort macro: the code below repeatedly…
- VBA Copy and Paste Worksheet: Practical ExamplesTo copy an entire worksheet in Excel VBA, use the worksheet’s Copy method and specify where the copy…
- Excel Macro Recorder: Is It Good Enough for Pro Work?For Excel, the macro recorder is a built-in way to capture repeatable actions and turn them into VBA;…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.