To find the last populated row in a specified Excel VBA range, use the range’s Find method with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. For a single column, End(xlUp) is a shorter option, but it needs extra care if the range is bounded or might be empty.
What “last row in a range” means
Before choosing a method, define what counts as the last row. In a single column, it usually means the row number of the lowest cell that contains data. In a rectangular range such as B2:D100, it generally means the lowest row in which any cell in those columns contains data. Those are different questions from finding the last used row anywhere on the worksheet.
Also decide what “contains data” means in your workbook. A cell can contain a constant, a formula, a formula that displays an empty string, or just formatting. The methods below search for cell contents, not merely formatting. The examples use worksheet row numbers, so a result of 7 means worksheet row 7, not the seventh row within the selected range.
Use End(xlUp) for a single column
For a conventional list in column B, with headers in row 1 and records starting in row 2, start at the bottom of the worksheet and move upward to the next non-empty cell. In Excel for Microsoft 365 on Windows, a macro can use this pattern:
Sub ShowLastRowInColumnB()
Dim ws As Worksheet
Dim lastRow As Long
' Work on the sheet in this workbook.
Set ws = ThisWorkbook.Worksheets("Sales")
' Start at the bottom of column B and move up.
lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
' Treat the header-only or empty list as having no data rows.
If lastRow < 2 Then lastRow = 0
MsgBox "Last data row: " & lastRow
End Sub
If the final record is in B27, lastRow is 27. If there are no entries below the header, the upward movement reaches row 1 and the example reports 0. That convention is useful because it distinguishes “no data rows” from a real worksheet row number. Change the test against 2 if your data starts on a different row.
This is a good fit when one particular column reliably identifies a record. For example, if every sales record has an order ID in column B, column B is a sensible anchor. If column B can be blank on a valid record while column C is filled, searching only B could stop too early; use a multi-column search instead.
Search a specific range with Find
For a bounded range, such as B2:D100, Find can search all of its cells and return the last match by row. This function returns the absolute worksheet row number, or 0 if it finds no contents:
Function LastRowInRange(ByVal searchRange As Range) As Long
Dim foundCell As Range
' A missing range has no matching row.
If searchRange Is Nothing Then Exit Function
' Find with this pattern expects one contiguous rectangular range.
If searchRange.Areas.Count <> 1 Then
Err.Raise vbObjectError + 1000, "LastRowInRange", _
"Pass one contiguous range."
End If
' Search formulas and constants, moving backward by row.
Set foundCell = searchRange.Find(What:="*", _
After:=searchRange.Cells(1, 1), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False, SearchFormat:=False)
' Find returns Nothing when the range has no matching cells.
If Not foundCell Is Nothing Then
LastRowInRange = foundCell.Row
End If
End Function
To call the function, place this code in a procedure in the same VBA project:
Sub ReportLastRowInSalesRange()
Dim ws As Worksheet
Dim dataRange As Range
Dim resultRow As Long
' Set the exact area whose contents should be checked.
Set ws = ThisWorkbook.Worksheets("Sales")
Set dataRange = ws.Range("B2:D100")
resultRow = LastRowInRange(dataRange)
MsgBox "Last row in the range: " & resultRow
End Sub
If the lowest row containing a formula or value in B2:D100 is row 18, the function returns 18, even if the match is in column D. If every cell in the range is empty, it returns 0. It accepts a single contiguous rectangle; a selection made from separate, non-adjacent areas is rejected rather than searched ambiguously.
How the Find settings affect the result
The arguments in the example are deliberate. What:="*" searches for content, SearchOrder:=xlByRows evaluates row order, and SearchDirection:=xlPrevious finds the last match in that order. After sets the starting point for the search; using the range’s first cell with the backward direction lets the search wrap through the range and reach its final matching row.
LookIn:=xlFormulas includes constants and formula cells, including formulas whose displayed result is an empty string. That can be appropriate when a formula still marks a row as part of the dataset. If your definition is based on displayed values, consider LookIn:=xlValues instead, and check the behavior against formulas in your own data. A formula returning "" can be treated differently from a genuinely empty cell depending on the search setting.
Excel remembers some Find settings from earlier searches, including searches made through the Find dialog. Supplying the important arguments explicitly makes VBA less dependent on a user’s previous search. SearchFormat:=False prevents a remembered formatting criterion from restricting the match. Avoid omitting settings merely because the macro appears to work on one computer.
Choosing between End and Find
Use End(xlUp) when one column is the reliable key and you want a compact, fast way to locate its last entry. Use Find when you need to search a particular rectangle or when valid rows can have gaps in the anchor column but content in other columns. For a multi-column area, a by-row Find search answers “what is the lowest row with anything in this range?” more directly.
- One dependable list column: use
Cells(Rows.Count, column).End(xlUp).Row, qualified with the worksheet as in the example. - Several columns define a record: use
Findon the exact rectangular range. - Only displayed results count: test a Find search using
xlValuesand formulas that return empty strings. - Formatting alone should not count: do not use a method that treats formatted cells as data; search for actual contents.
There is no universal “last row” independent of the data rule. If column B holds IDs, its last row may be 27, while a note in column D on row 30 makes the last content row in B:D equal to 30. Select the method that matches the operation you plan to perform, such as resizing a table, copying records, or clearing a bounded input area.
Common edge cases and mistakes
A common mistake is using Range("B2").End(xlDown) to find the bottom of a list. An empty cell within the list can interrupt that movement, and the result may not be the last occupied row. Starting at the worksheet bottom and moving up in a known column avoids that particular gap problem.
Another trap is assuming that End(xlUp) stays inside a smaller range. If you start at B100 and the cells B2:B100 are blank but B1 contains a heading, it can return row 1. Check the returned row against your data’s first row, as the column example does, or use Find on the bounded range. A search of B2:D100 cannot accidentally include a value above row 2 or below row 100.
Do not confuse the last content row with a worksheet’s used range or last formatted cell. Formatting applied far below the data can make a sheet appear much larger, and worksheet-level “last cell” approaches can reflect that history rather than the last meaningful record. When you need the last row in a particular input area, search that area explicitly.
Finally, be precise about which worksheet the code refers to. Unqualified references such as Cells or Range may point to the active sheet, which can change while a macro runs. Qualify them with a worksheet variable, such as ws.Cells and ws.Range, so the target is clear.
Running VBA safely in Excel
These examples are for desktop Excel. In Microsoft 365 for Windows, the Visual Basic Editor can be opened with Alt+F11; on Mac, the usual shortcut is Option+F11, though keyboard settings may affect function-key behavior. The Visual Basic Editor and VBA are available in desktop Excel for Mac, but Excel for the web cannot create, edit, or run VBA macros. Use desktop Excel to run this code.
Save the workbook as a macro-enabled workbook, typically .xlsm, if it needs to retain VBA. Save a copy before running a macro: macro actions cannot be undone with Ctrl+Z, even though the examples above only report a row number. Run macros only in workbooks you trust, and do not enable macros in an unfamiliar file just to inspect its contents.
Frequently Asked Questions
How do I find the last used row in column A? Use ws.Cells(ws.Rows.Count, "A").End(xlUp).Row, where ws is the worksheet you intend to search. If row 1 is a header and there are no records, add a check such as If lastRow < 2 Then lastRow = 0.
How can I find the last row with data in several columns? Apply Find to the combined rectangular range, such as A2:F500, with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. The result is the lowest row containing a match anywhere in that rectangle.
Does End(xlUp) work if there are blank rows? Yes, for a single column it moves upward from the bottom and stops at the next non-empty cell, so blank rows between records do not normally prevent it from finding the lowest entry. It cannot detect a record that is blank in the column you chose as the anchor.
Why does Find return a row for a formula that looks blank? The cell still contains a formula, even if its displayed result is an empty string. The example searches with LookIn:=xlFormulas, so formulas count. If only visible results should count, try xlValues and validate the result with your workbook’s formulas.
Can I run this macro in Excel for the web? No. Excel for the web can preserve a workbook containing VBA, but it does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the code.
Related
- Excel MAXIFS: Find the Highest Value for Each Date
- Excel Tabs: How to Find Duplicate Names Across Sheets
- Excel SUM with Multiple Criteria: How to Do It
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Documents Folder in Excel VBA: Open It SafelyTo open the current Windows user’s Documents folder from Excel VBA, get its location with the Windows Script…
- Tiny Excel Macro: How to Record Small Repetitive Tasks in ExcelTo record a tiny task in Excel for Microsoft 365 on Windows, open the Developer tab, choose Record…
- How to Concatenate Strings in VBA Using & and JoinIn VBA, concatenate strings by joining them with the ampersand operator (&), such as fullName = firstName &…
- Excel VBA Dictionary: How It Works and When to Use ItYes, Excel VBA can use a Dictionary on Windows, but VBA does not include a native Dictionary type.…
- 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…
- Excel and Access VBA: What It Is and How to Use ItExcel and Access VBA (Visual Basic for Applications) is a programming language built into Microsoft Office that lets…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.