To find the last nonblank row in column A, use lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, where ws is the worksheet you want to check. To find the last row containing anything anywhere on the sheet, use Excel’s Find method instead; the right choice depends on what your data counts as its last row.
What “last row” means in VBA
Excel has a fixed maximum row number, but that is not usually the row a macro needs. In everyday VBA, the last row means the final row containing relevant data. That could mean the last nonblank cell in a particular column, the last row with a value in any column, or the last row in an Excel table. These definitions can return different answers on the same worksheet.
For example, imagine a list where column A contains customer names, column B contains order dates, and column C contains notes. If the macro processes customers, column A may be the best anchor. If some names are missing but dates are always present, column B may be safer. If notes extend below the main list, searching the entire worksheet may return a row that is not part of the customer data.
Decide what counts as a record before choosing code. A worksheet’s visible formatting, an old value outside the current list, or a formula that displays an empty string can affect some methods. A reliable macro makes its search scope and definition of “data” explicit.
The simple column-based method
The familiar End(xlUp) approach starts at the bottom of a worksheet and moves upward in one column, like pressing Ctrl+Up from the bottom of that column. Because the worksheet row count is used as the starting point, this works even when the list grows beyond a previously assumed limit such as row 1000.
Sub FindLastRowInColumnA()
Dim ws As Worksheet
Dim lastRow As Long
' Set the worksheet explicitly.
Set ws = ThisWorkbook.Worksheets("Data")
' Find the last nonblank cell in column A.
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
MsgBox "Last row in column A: " & lastRow
End Sub
This example assumes the worksheet is named Data and that column A is a meaningful anchor. Replace the sheet name and column letter to match the workbook. The variable is declared as Long, which is appropriate for Excel row numbers and avoids the small range of the older Integer type.
If column A has no entries at all, the expression returns 1: starting at the bottom and moving upward reaches the top cell. That result does not prove that row 1 contains data. If an empty column must return zero instead, check it first:
Sub FindLastRowOrZero()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
lastRow = 0
Else
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
End If
MsgBox "Last row: " & lastRow
End Sub
CountA counts cells that are not empty, including cells containing formulas. A formula that returns an empty string is still a formula, so it is counted. That distinction matters when a sheet uses formulas to make unused rows look blank.
Find the last used row anywhere on a worksheet
When the last record may be identified by any populated column, search across the worksheet rather than choosing one column. Range.Find searches cells matching a pattern, and SearchOrder:=xlByRows with SearchDirection:=xlPrevious locates the last matching row in row order. The example below looks for any content, including formulas.
Sub FindLastUsedRowOnSheet()
Dim ws As Worksheet
Dim hit As Range
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
' Search all cells, including formulas, from the end of the sheet.
Set hit = ws.Cells.Find(What:="*", _
After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)
If hit Is Nothing Then
lastRow = 0
Else
lastRow = hit.Row
End If
MsgBox "Last row with content: " & lastRow
End Sub
The If hit Is Nothing check is important: when the sheet has no matching cells, Find returns no range object. Trying to read hit.Row without checking can cause a runtime error. The search explicitly supplies its main options because Excel remembers some Find settings from earlier searches, including searches performed through the Find dialog. Supplying the arguments makes the macro’s behavior more predictable.
With LookIn:=xlFormulas, a formula is considered content even if its displayed result is an empty string. If the intended definition is the last cell with a displayed value, use LookIn:=xlValues instead and verify that behavior against the formulas and data in your workbook. Formulas, errors, and text that looks blank can make that distinction consequential.
Choose the method that matches the data
There is no single last-row expression that is best for every worksheet. Match the method to the structure of the data and to the operation the macro will perform.
- One reliable key column: Use
End(xlUp)in that column. It is concise and easy to read when every valid record has an entry in the chosen field. - Records may have gaps in the key column: Use
Findacross the relevant worksheet area, or use a different column that is guaranteed to be populated. - Data is stored in an Excel table: Use the table’s data-body range, which directly represents its rows of records.
- Only a defined block matters: Restrict a search to that block rather than searching the entire sheet, so unrelated notes or content do not affect the result.
A common misconception is that UsedRange always identifies the final row of meaningful data. It reports Excel’s used range, which can be influenced by cells that have been edited or formatted. It may include empty-looking cells beyond the actual records, and it may not express the business rule for which rows belong to a list. It can be useful for other tasks, but it is not automatically a better substitute for a deliberate column or content search.
Working with Excel tables
If the list is an Excel table, its structure is often a more reliable source than scanning worksheet cells. Tables grow as records are added, and their data body excludes the header row. The following example assumes the table is named SalesTable and is on the Data worksheet.
Sub FindLastTableRow()
Dim ws As Worksheet
Dim tbl As ListObject
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
Set tbl = ws.ListObjects("SalesTable")
' An empty table has no DataBodyRange.
If tbl.DataBodyRange Is Nothing Then
lastRow = 0
Else
lastRow = tbl.DataBodyRange.Rows(tbl.DataBodyRange.Rows.Count).Row
End If
MsgBox "Last table row: " & lastRow
End Sub
An empty table still has headers, so checking DataBodyRange handles the case where there are no data rows. If you need the number of records rather than the worksheet row number, use the data body’s row count instead. Those figures are different when the table begins below row 1: a table might contain five records while its final worksheet row is 12.
Common errors and how to avoid them
- Using an unqualified
Cellsreference: Code such asCells(Rows.Count, 1)uses the active sheet. If another sheet becomes active, the macro can inspect the wrong place. Qualify both references with the worksheet, as inws.Cells(ws.Rows.Count, 1). - Choosing a column that has blanks:
End(xlUp)finds the last nonblank cell in that column, not the last record elsewhere on the sheet. Select a column populated for every record or use another method. - Assuming an empty column returns zero: The simple
End(xlUp)expression returns row 1 for an empty column. Add a check if zero is the required empty-data result. - Confusing formatting with content: A cell can be formatted without containing a value. The
End(xlUp)pattern looks for nonblank cells in its selected column, while an explicit content search withFindsearches for matching content; neither should be treated as a test for every kind of formatting. - Leaving out Find options: Excel can reuse Find dialog settings. Set the search options in code, and handle the case where no match is found.
- Searching too broadly: A title, footnote, or stray value far below a list may become the worksheet’s last used row. Search a relevant range or use the table boundary when unrelated content should be ignored.
For example, if column A contains names through row 80 but a comment remains in column F at row 500, the column-A method returns 80 while a whole-sheet search returns 500. Neither answer is inherently wrong: the first answers “last name in column A,” and the second answers “last row containing content anywhere.” Your macro should use the answer that matches what it will process.
Run the macro safely
In Excel for Microsoft 365 on Windows, open the VBA editor with Alt+F11, choose Insert > Module, paste the procedure into the module, and run it with F5 while the cursor is inside that procedure. You can also run a saved macro from Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon and select Developer.
On a Mac, open the Visual Basic Editor from the Developer tab’s Visual Basic command; the available keyboard shortcut can vary with the keyboard and function-key settings. Excel for the web can open and edit many workbooks but cannot create, run, or edit VBA macros. Use the desktop Excel application for these procedures. Macro-enabled workbooks are commonly saved in the .xlsm format so the VBA project can be retained.
Save a copy of the workbook before running a macro that changes data. VBA actions generally cannot be undone with Ctrl+Z, and the sample procedures display a result but do not modify worksheet cells. Only enable macros from sources you trust; do not lower Excel’s macro security settings just to run code from an unknown file.
Frequently Asked Questions
What is the shortest VBA code to find the last row in column A?
With a worksheet variable named ws, use lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. It finds the last nonblank cell in column A; it returns 1 if that column is empty.
How do I find the last row with data in any column?
Use Find on ws.Cells with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. Check whether the returned range is Nothing before reading its row number, and specify the Find options explicitly.
Does End(xlUp) find cells with formulas?
Yes. A cell containing a formula is nonblank to this method, even when the formula displays an empty string. If that is not how you want to define the last row, use a suitable search rule or inspect the values your workbook treats as records.
Why does my last-row macro return the wrong row?
Common causes include checking a column with missing entries, relying on the active worksheet, or searching the whole sheet when unrelated content exists below the list. Confirm which column or range defines a valid record and qualify references with the intended worksheet.
Can I use the last row of an Excel table instead?
Yes. Get the table with ws.ListObjects("SalesTable") and inspect its DataBodyRange. Check whether that range is Nothing, because a table with headers but no records has no data body.
Can I run VBA in Excel for the web?
No. VBA macros must be run in desktop Excel. Excel for the web does not run VBA, so open the workbook in the desktop app to use the macro.
Related
- University Budget Template in Excel: What to Include
- Unmerge Excel Cells Without Losing Data
- Unmerge Cells in Excel on iPad: Steps, Limits, and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Loop Continue: How to Skip to the Next IterationVBA has no built-in Continue For, Continue Do, or general Continue statement. To skip the rest of the…
- VBA Break Function: How to Pause or Stop a Running MacroVBA does not have a built-in Break function or statement. To interrupt a running macro, use the Visual…
- VBA InputBox With a Drop-Down List: Your OptionsNo: VBA’s built-in InputBox does not include a drop-down list, and Application.InputBox does not add one. To let…
- Excel VBA: Find the Bottom Row of Data in a SheetTo find the bottom row in a particular Excel column, start at the worksheet’s last row and move…
- VBA InputBox Prompt: Syntax, Examples, and Common FixesIn VBA, use the InputBox function to display a prompt and collect text from a user: assign its…
- Excel VBA Set Column Width: Code, Units, and Common FixesTo set a column width in Excel VBA, assign a value to the column’s ColumnWidth property, such as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.