In Excel VBA, use the worksheet’s Find method to locate the last cell containing content anywhere on the sheet, then read its row number. Search backward by rows and check for Nothing so the code also handles an empty worksheet safely.
What “last used row” means
The phrase can describe two different things: the last row containing content anywhere on a worksheet, or the last row containing content in a particular column. Those answers can differ. If column A ends at row 20 but column F contains a note in row 35, the worksheet-wide answer is 35; the answer for column A is 20.
It is also useful to decide what “content” means for your task. A cell can contain a constant, such as a typed number or text, or a formula. A formula may display an empty string while still existing in the cell. The method below uses LookIn:=xlFormulas, so a formula counts even when its displayed result looks blank. Formatting alone, such as a fill color applied to an otherwise empty cell, is not content for this search.
Making this definition explicit matters in automation. A macro that appends records may need the last occupied row in the records column. A macro that copies or clears all worksheet content may instead need the final row used in any column.
Recommended worksheet-wide Find method
Use a worksheet object rather than relying on whichever sheet happens to be active. The function below searches the full worksheet, starting from the bottom of the search order, and returns 0 if it finds no content. That 0 is a convenient empty-sheet signal; valid Excel row numbers start at 1.
Public Function LastUsedRow(ByVal ws As Worksheet) As Long
Dim lastCell As Range
' Return 0 for an empty sheet or an unassigned worksheet.
If ws Is Nothing Then Exit Function
' Search for content, checking the lowest rows first.
Set lastCell = ws.Cells.Find(What:="*", _
After:=ws.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
SearchFormat:=False)
If Not lastCell Is Nothing Then
LastUsedRow = lastCell.Row
End If
End Function
To use this VBA, open the Visual Basic Editor in desktop Excel, insert a standard module, and place the function there. On Windows, Alt+F11 opens the editor; on Mac, use Developer > Visual Basic (the keyboard shortcut can depend on the keyboard and function-key settings). Excel for the web cannot run VBA macros, so run the code in the desktop application. Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z.
For example, put this calling procedure in the same module and change the sheet name to match your workbook:
Public Sub ShowLastUsedRow()
Dim ws As Worksheet
Dim rowNumber As Long
Set ws = ThisWorkbook.Worksheets("Data")
rowNumber = LastUsedRow(ws)
If rowNumber = 0 Then
Debug.Print "The worksheet is empty."
Else
Debug.Print "Last used row: " & rowNumber
End If
End Sub
How the Find arguments affect the result
What:="*" searches for content using Excel’s wildcard for a sequence of characters. SearchOrder:=xlByRows makes the search order row-based, and SearchDirection:=xlPrevious moves backward through that order. Together, these settings find the content in the greatest row number, even if it is in a column far to the right.
LookIn:=xlFormulas searches formulas and constants as stored in cells. This is often the appropriate definition for “used” in a macro: a formula cell counts even when its current result is an empty string. If you want to define use by displayed values instead, consider LookIn:=xlValues, but check the behavior against formulas and data in your workbook before relying on it.
The other arguments make the search more predictable. LookAt:=xlPart allows a partial match; MatchCase:=False avoids case-sensitive matching; and SearchFormat:=False prevents a previously selected search format from affecting the result. Excel remembers some Find settings between searches, so specifying the important options in VBA is safer than depending on settings left by a user.
The After argument identifies the cell from which Excel begins. With a backward search, the specified start cell does not mean the code is limited to that cell or to its column. The search still covers ws.Cells, the complete cell range of the worksheet.
Find the last row in one column
If your data has a required key column, such as an ID in column A, a shorter technique is to start at the bottom of that column and move upward to the first nonempty cell. This is usually the clearest choice when the task is specifically about that column, rather than the worksheet as a whole.
Public Sub GetLastRowInColumnA()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
Debug.Print "Last nonempty row in column A: " & lastRow
End Sub
ws.Rows.Count supplies the last row number available on that worksheet, so the code does not hard-code a row limit. In current Excel worksheets that limit is 1,048,576. The End(xlUp) operation behaves like moving upward from the bottom of the column to the next occupied cell.
There is an important empty-column caveat: if column A contains no content, this expression returns 1, because the upward move reaches the top of the column. If 1 must mean “no data” in your process, add a check for whether the top cell is empty, or use a worksheet-wide Find function that returns 0 for an empty sheet. Also note that a formula returning an empty string is still a formula in the column and can affect the result.
Choosing between Find, End, and UsedRange
Each approach answers a slightly different question. Choose based on the shape of the data and what the macro will do:
- Use worksheet-wide Find when content might appear in any column and you need the maximum occupied row across the sheet. It handles an empty sheet explicitly and can be set to count formulas.
- Use End(xlUp) when a specific column reliably represents the records. It is compact and easy to understand, but an empty column needs special handling and content outside that column is ignored.
- Use UsedRange when you need Excel’s recorded used area, including its boundary across rows and columns, rather than a carefully defined content-only last row. It can include cells that were formatted or edited and later cleared.
For example, ws.UsedRange.Rows.Count is not always the absolute last worksheet row number. The used range may begin below row 1, so its row count is only the height of that range. To get its ending row, add the starting row to the range height and subtract 1. Even then, UsedRange can reflect past formatting or edits, making it a less dependable answer when you mean “last cell that currently contains data.”
Similarly, Ctrl+End moves to Excel’s recorded lower-right used cell, but it is not a substitute for defining your macro’s rule. A worksheet can have formatting far below its records, and the recorded boundary may remain larger than expected. If the result matters to a procedure, search according to the content criteria that procedure actually needs.
Common problems and edge cases
Empty worksheet: Find returns the special value Nothing when it finds no match. Reading lastCell.Row without checking for this causes an object-related runtime error. The function checks the result before reading its row.
Wrong active sheet: Unqualified code such as Cells.Find(...) acts on the active sheet. If a different worksheet is active when the macro runs, it can report the wrong row. Use ws.Cells.Find(...) and set ws to the intended worksheet explicitly.
Formulas that look blank: A cell containing =IF(...,"",...) is not an empty cell merely because nothing is displayed. With LookIn:=xlFormulas, it counts as occupied. If your definition is “a row with a visible result,” decide how to treat such formulas and test the search settings on representative data.
Hidden rows and filters: The search covers hidden rows as well as visible rows. Applying a filter does not make hidden records disappear from the worksheet-wide search. If the task is to find the last visible row only, that is a different problem and requires checking row visibility in addition to finding content.
Formatting is not content: A colored or bordered blank cell should not be treated as a data row by this Find pattern. If formatting defines your boundary for a special task, use a method designed to inspect formatting rather than assuming a content search will find it.
Multiple areas and gaps: Blank rows in the middle do not prevent Find from locating content farther down. For example, if rows 2–10 contain records and row 25 contains a note, the worksheet-wide function returns 25. If you need the last complete record instead, search only the record-defining column or validate the row’s required fields.
Using the result safely in a macro
Once you have a row number, treat it as a boundary, not proof that every row above it is a valid record. A worksheet might contain titles, footnotes, totals, or stray entries. For an append operation, a common pattern is to use the next row after the last record, but first verify that the selected key column is the right one and define what should happen when there are no records.
Keep the worksheet reference explicit and avoid changing the active sheet just to perform a search. This makes code less sensitive to the user’s current selection. For important macros, test at least these cases before using the workbook on live data:
- A completely empty sheet.
- Data only in the first row or in the final worksheet row.
- A gap between records and a content cell below the gap.
- A formula that displays an empty string.
- Content in a column beyond the main table, and a filtered or hidden row.
These checks expose the difference between worksheet-wide content and a data table’s final record. They also help prevent a macro from overwriting data or appending records in the wrong place.
Frequently Asked Questions
How do I find the last row with data in any column using VBA?
Search ws.Cells with Find, using SearchOrder:=xlByRows and SearchDirection:=xlPrevious. Return the found cell’s Row, after checking that Find did not return Nothing.
Does Find count a formula that displays a blank?
With LookIn:=xlFormulas, the formula itself is searched, so it counts even if its result displays as an empty string. That is useful when “used” means a cell contains a formula, but may not match a definition based only on visible results.
Why does End(xlUp) return row 1 when the column is empty?
The code starts at the bottom of the column and moves upward. With no nonempty cell to stop at, it reaches the top, row 1. Add an empty-column check or use a function that returns 0 when the worksheet contains no content.
Does UsedRange give the last row containing data?
Not necessarily. Its boundary can include cells that were formatted or used previously, even if their contents have since been cleared. It describes Excel’s recorded used area, which is not always the same as the last row with current content.
Can I run Excel VBA in a browser or on a Mac?
Excel for the web does not run VBA macros; use desktop Excel. Excel for Mac supports VBA, though the route to the Visual Basic Editor and keyboard shortcut can differ from Windows. The Find-based approach itself uses standard Excel VBA worksheet objects.
Related
- Excel Template for a Social Media Calendar
- Excel Job List Template: Track Applications and Follow-Ups
- Excel To-Do List Templates: How to Choose and Use One
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Code to Refresh Pivot Tables Automatically in ExcelTo refresh every PivotTable in the workbook that contains your VBA, run a macro that loops through each…
- Excel Macro Arrays: How VBA Arrays WorkAn Excel macro array is a VBA variable that holds multiple values, so a macro can work with…
- VBA Functions With Parameters: Syntax and ExamplesA VBA function with parameters accepts values from its caller, uses them in its code, and returns a…
- Excel UserForm Grid Size: Spacing, Settings, and LayoutIn Excel VBA, a UserForm’s design grid is adjustable in the Visual Basic Editor; it does not have…
- Excel VBA Input Form: How to Build and Use a UserFormAn Excel VBA input form is a custom window, usually built as a VBA UserForm, that lets people…
- VBA Loop Through Cells: Methods and ExamplesTo loop through cells in Excel VBA, use For Each for a specified range or a counter-based For…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.