To find the last row containing data within a particular Excel range, use the range’s Find method with a bottom-to-top, row-wise search. The code below searches B2:E1000, returns the worksheet row number of the last match, and handles the case where the range is empty.
Use Find to search the specified range
This example looks for any nonempty cell in the specified range, including cells with formulas. It uses xlFormulas so a formula is treated as content even if its displayed result looks blank.
Sub FindLastRowInRange()
Dim ws As Worksheet
Dim searchRange As Range
Dim lastCell As Range
Dim lastRow As Long
Set ws = ThisWorkbook.Worksheets("Data")
Set searchRange = ws.Range("B2:E1000")
' Search by row, starting at the bottom of the range.
Set lastCell = searchRange.Find( _
What:="*", _
After:=searchRange.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False, _
MatchByte:=False, _
SearchFormat:=False)
If lastCell Is Nothing Then
lastRow = 0 ' No matching content in the range.
Else
lastRow = lastCell.Row
End If
Debug.Print lastRow
End Sub
Change the worksheet name and range address to suit your workbook. The code prints the result in the Immediate window in the Visual Basic Editor; replace Debug.Print lastRow with the next action your macro needs. Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, even though this sample only searches and prints a result.
How the search works
Find searches within searchRange, rather than across the whole worksheet. What:="*" uses the asterisk wildcard to match content, while SearchOrder:=xlByRows makes Excel move through rows. SearchDirection:=xlPrevious searches backward from the specified starting point, so the first match it returns is the last matching row in the range.
The After argument must refer to a single cell inside the range. In this example it is the range’s first cell. Excel begins after that cell and wraps around the range; with a backward search, this setup still checks the full range and returns its last matching cell. The other arguments are supplied explicitly because Excel remembers some Find settings from earlier searches, including searches made through the Find dialog. Setting them avoids relying on a prior user or macro setting.
The result, lastCell, is a Range object representing one matching cell, not an entire row. Its Row property gives the worksheet row number. For example, if the last content in B2:E1000 is in D27, lastCell.Row is 27.
Choose the exact area to search
The range definition controls what “last row” means. If a worksheet has notes below a table, searching the whole sheet can return a row belonging to those notes rather than the table. Limit the search to the table’s relevant columns and rows, or use a structured table’s data range if the table is the intended scope.
- Several data columns: Search a block such as B2:E1000. The result is the bottommost row with a match anywhere in those columns.
- One column: Search a single column range, such as B2:B1000, when only that column defines whether a record exists.
- A range starting below row 1: The returned
.Rowis still the worksheet row number, not a position counted from the top of the range.
Be deliberate about the top and bottom limits. If the range ends at row 1000, content at row 1001 is invisible to this search. If you instead set the search range to entire columns, it will include far more cells than a small table requires. A bounded range is often easier to reason about and can avoid searching irrelevant worksheet space.
Formulas, displayed blanks, and what counts as data
With LookIn:=xlFormulas, a cell containing a formula is considered occupied even when that formula returns an empty string, such as a formula whose result is "". This is often the right definition when the question is “How far down have formulas or entries been placed?” It also means that formatting alone does not count as content: a cell that is only colored or has a border is not a match for this content search.
Sometimes the desired definition is different: a row should count only if it displays a value. In that case, decide how to treat formulas that return empty strings, zeroes, errors, and spaces before changing the search logic. A formula returning "" is not the same thing as a truly empty cell, and a cell containing a space may look blank while still containing text. Excel’s COUNTA also counts cells containing formulas that return empty text, so it is not a shortcut for “visibly nonblank.”
If visible results rather than entered formulas define the boundary, test the method against representative cells in your workbook, especially formulas returning blank text and numeric values. For more specialized rules, loop through the candidate rows and inspect each cell’s value or formula result explicitly. The right test depends on whether a zero, an error, a space, or a formula-generated blank should make a row count.
Return a relative row number or the matching cell
Many macros need the worksheet row number so they can select a record or set a range. The sample returns that number through lastCell.Row. Other code may need the cell address, the column, or the entire row; because the search returns a Range, those can be obtained from the same match without searching again.
When a range begins at row 2, its first row has worksheet row number 2. If a later procedure instead expects an index relative to the range, subtract the range’s starting row and add one. For example, for a search range beginning at row 2 and a match at worksheet row 27, the relative position is 26. Keeping worksheet row numbers and range-relative positions distinct prevents off-by-one errors.
You can also use the matching cell to identify which column contained the last match. In a multi-column search, the bottommost row is the main answer, but there may be multiple matches on that row. Find returns one cell according to its search order; it does not tell you that all cells in that row are filled. If a later operation needs the whole record, use the returned row number to refer to the intended record columns.
How Find compares with End and UsedRange
For a range spanning multiple columns, Find is a strong general choice because it searches the whole block and can locate the bottommost row with content anywhere in it. Two common alternatives answer narrower or less reliable questions.
End(xlUp)in one column: This is concise when a particular column is guaranteed to be filled for every record. Starting at the bottom of that column and moving up finds its last nonblank cell. It can miss later records if that chosen column has a gap, so it does not by itself answer “last row with data anywhere in B:E.”UsedRange: This represents the worksheet’s used area, which can be affected by previous editing and formatting. Its last row is not necessarily the last row with meaningful data in a particular table. It is a poor substitute for defining the relevant columns and searching their contents.CountA: Counting nonempty cells can be useful for other tasks, but a count does not identify the last row. It also counts formula cells whose result is empty text, which may not match a visible-data definition.
Use End(xlUp) when one reliable key column defines the records. Use Find when any cell across a defined range can establish the last row. Avoid treating the worksheet’s used boundary as a synonym for the last meaningful record.
Common problems and how to avoid them
Object variable not set: If no cell matches, Find returns Nothing. The If lastCell Is Nothing check is essential; trying to read lastCell.Row without it causes an error. In the sample, zero is a chosen sentinel meaning “no matching row.” You could instead leave a result variable empty or handle the empty case with a message.
The wrong worksheet is searched: Qualifying the range through ThisWorkbook.Worksheets("Data") avoids silently searching whichever sheet happens to be active. ThisWorkbook means the workbook containing the macro. If the code is stored in an add-in or another workbook and should search the workbook the user is viewing, select the workbook reference intentionally rather than relying on an unqualified Range.
Unexpected matches after a previous Find: If arguments such as LookIn, LookAt, or SearchOrder are omitted, Excel may reuse settings from an earlier Find operation. Specify the options needed for a repeatable search, as the sample does. Also keep SearchFormat:=False unless formatting is intentionally part of the search.
Merged cells or unusual layouts: Merged cells can make range operations less predictable, and they complicate what counts as a row of data. For a reliable table boundary, avoid merged cells in the data area when possible. If the range uses them, test the exact layout and decide whether the merged cell’s top-left cell is the intended indicator.
Searching too much or too little: A fixed range can be fast and clear but must be large enough to include future records. Entire-column ranges accommodate growth but search a much larger area. Choose a boundary based on how the workbook grows, and ensure that the range does not include unrelated notes or a second table underneath.
Pick the method based on the data rule
Before coding, state the rule in plain language. “Last row with a formula or entry anywhere in B:E” points to the bounded Find example. “Last row with a customer ID in B” points to a search of column B or a column-specific End(xlUp) approach. “Last row with a displayed result” requires a decision about formulas returning blanks and should be tested against the workbook’s actual contents.
For regularly growing data, an Excel Table can make the record boundary clearer than a manually chosen maximum row. A table has a defined data body and expands as records are added; a macro can work with the table’s data range rather than guessing how far down a fixed block might extend. This does not remove the need to define what qualifies as a record, but it can make the range easier to maintain.
Finally, decide what the macro should do when no data exists. Returning zero is convenient when valid worksheet rows start at 1, but downstream code must check for zero before using it as a row number. Returning a Boolean status alongside the row, or handling the empty case immediately, can make larger procedures clearer.
Frequently Asked Questions
How do I find the last used row in specific columns with VBA?
Set a Range variable to those columns and call its Find method with SearchOrder:=xlByRows and SearchDirection:=xlPrevious. Check whether the returned range is Nothing before reading its row.
Does this method include formulas that display nothing?
Yes, the sample uses LookIn:=xlFormulas, so a formula counts even if its result is an empty string. If those cells should be ignored, define and test a values-based rule rather than assuming a formula cell is truly empty.
What does the macro return if the range is empty?Find returns Nothing. The sample sets lastRow to 0 in that case, then prints the value. Choose a different empty-range response if zero could be confused with another result in your procedure.
Why not use Ctrl+Down or End(xlUp)?
Those are useful for navigating or finding the edge of a contiguous region in one column. A gap can affect the result, and a single column cannot detect a later entry that exists only in another searched column. Use them when one dependable column defines the data; use a multi-column Find search when any column can contain the last record’s data.
Will the code work in Excel for Mac or Excel for the web?
VBA macros can run in desktop Excel for Windows and Mac, though the Visual Basic Editor and keyboard shortcuts differ by platform. Excel for the web does not run VBA macros; use desktop Excel to run this code. On Windows, open the Visual Basic Editor with Alt+F11. On Mac, the editor can be opened from Tools > Macro > Visual Basic Editor; keyboard shortcuts can vary by keyboard and Excel settings.
Related
- Excel Shortcut for Inserting Rows Above or Below
- Excel Shortcut to Go to a Specific Column in Seconds
- Excel Shortcut to Insert a Cut Row
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Dictionary: Search by Key and Check Whether It ExistsTo search a VBA Dictionary by key, call its Exists method before retrieving the value: use If dict.Exists(searchKey)…
- VBA Range.Resize: Change a Range’s Dimensions in ExcelIn Excel VBA, use the Resize property to return a range with a different number of rows or…
- Excel VBA Link to Worksheet: Methods and ExamplesIn Excel VBA, link to a worksheet by referring to it with ThisWorkbook.Worksheets("SheetName"); to create a clickable link…
- Excel VBA Split: Separate Text with the Split FunctionExcel VBA’s Split function separates a string wherever it finds a specified delimiter and returns the resulting pieces…
- Concat in Excel VBA: Join Text with the & OperatorTo concatenate text in Excel VBA, use the ampersand operator, &, to join values, such as fullName =…
- Writing VBA for Pivot Tables in Excel: A Practical GuideYes—you can write VBA to create, refresh, and configure Excel PivotTables, including their row, column, and value fields.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.