Excel VBA: Find the Last Row With a Specific Value

VBA & macros · Updated November 19, 2026

To find the last row containing a particular value, search the target column with VBA’s Range.Find method, set SearchDirection:=xlPrevious, and check whether a match was returned. For example, the macro below searches column A for the exact value Approved and reports the row number.

Example: Find the Last Exact Match

This example searches an explicitly named worksheet and the whole of column A. Searching in reverse means the first match returned is the last matching cell from the top of the sheet. Replace the worksheet name, column, and target value with the ones in your workbook.

Option Explicit

Sub FindLastMatchingValue()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim searchValue As Variant

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Columns("A")
    searchValue = "Approved"

    Set foundCell = searchRange.Find( _
        What:=searchValue, _
        After:=searchRange.Cells(1, 1), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "No match was found."
    Else
        MsgBox "Last match is in row " & foundCell.Row
    End If
End Sub

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z. This code only searches and displays a message; it does not change cell contents. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want to retain the macro.

What the Find Settings Mean

Being explicit about the search options matters because Excel remembers some Find settings from earlier searches, including searches made through the Find dialog. Leaving an option out can make a macro behave differently depending on what someone searched for previously.

  • What:=searchValue sets the value or text to look for.
  • LookIn:=xlValues searches cell values, including the calculated results displayed by formulas. Use xlFormulas instead when the formula text itself is what you need to search.
  • LookAt:=xlWhole requires the entire cell value to match. For example, searching for Approved will not match Not Approved.
  • SearchDirection:=xlPrevious searches backward, so the last match in the selected range is returned first.
  • After:=searchRange.Cells(1, 1) sets the starting reference. Find begins after that cell and wraps around the range; with a one-column range and backward search, it reaches the bottom of the column first.
  • MatchCase:=False makes a text search case-insensitive. Set it to True if capitalization must match.
  • SearchFormat:=False prevents an old formatting condition from restricting the result.

The search range determines what “last” means. When the range is A:A, the result is the last matching cell in column A, including a possible header. If the relevant records occupy only A2:A500, search that smaller range to exclude the header and unrelated cells.

Get the Row Number or Use the Matching Cell

The macro reports foundCell.Row, which is the worksheet row number. The returned Range object is more useful than a number alone when the next step is to read or update data on the same record. For example, if a match is in A27 and the corresponding status date is in column C, ws.Cells(foundCell.Row, "C").Value refers to that date cell.

To keep the result available after the search, assign it to a variable or use its properties in the code that follows. Common useful properties include foundCell.Address for the cell address, foundCell.Value for its value, and foundCell.Row for the row number. Always test foundCell Is Nothing before reading those properties: a search with no match does not return a cell.

If the match should identify a complete record, keep the search limited to the key column rather than searching an entire table. A value might appear in a notes or comments column on a later row, even though that row is not the record you intended to identify.

Search Formula Results or Formula Text?

Choose xlValues when you want to find the result shown in a cell. For example, if B20 contains a formula that calculates Approved, a search in B:B with LookIn:=xlValues can find that result. This is usually the right choice when the question is which record currently has a particular status.

Choose xlFormulas when searching the contents of formulas, constants, or text as stored in cells. This is useful for investigating whether a formula or a reference appears in a range. It is not a substitute for searching a formula’s calculated result: the formula text may be something like =IF(C20>0,"Approved","Pending"), not the word displayed after calculation.

Formatting can also affect what people see without changing the underlying value. A number may display as a date, for example, or a custom number format may hide digits. If a search seems surprising, inspect the cell’s actual value and formula, and confirm whether the intended criterion is the stored value, displayed result, or formula text.

Limit the Search to the Data Rows

Searching a whole column is simple and often appropriate, but it can include headings, notes, or old data far below the current table. If a header cell happens to contain the target text, it can become the result when no data row matches. A defined range such as A2:A1000 avoids that issue when the data boundaries are known.

For a changing list, determine the last occupied row in the key column and build a range from the first data row to that endpoint. One conventional VBA technique is ws.Cells(ws.Rows.Count, "A").End(xlUp).Row. It moves upward from the bottom of column A to the last nonempty cell. This is useful when the column is consistently populated for every record, but it is not a universal definition of the last data row: formulas returning an empty string still occupy cells, and gaps or incomplete records may require a different boundary rule.

When using a calculated endpoint, handle the case where the column has no data below its header. Otherwise, a range built from row 2 to row 1 may be invalid or may not represent the intended search area. A fixed table range, a properly maintained Excel Table, or a separate reliable key column can make the boundary clearer.

When a Bottom-Up Loop Is a Better Fit

Find is concise and well suited to searching a range. A loop can be easier to adapt when the comparison needs custom rules, such as checking a normalized value or applying additional conditions from other columns. This version searches upward through the used portion of column A and compares values with VBA’s equality operator.

Sub FindLastMatchingValueWithLoop()
    Dim ws As Worksheet
    Dim lastDataRow As Long
    Dim rowNumber As Long
    Dim searchValue As Variant
    Dim cellValue As Variant

    Set ws = ThisWorkbook.Worksheets("Data")
    searchValue = "Approved"
    lastDataRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For rowNumber = lastDataRow To 2 Step -1
        cellValue = ws.Cells(rowNumber, "A").Value2

        If Not IsError(cellValue) Then
            If cellValue = searchValue Then
                MsgBox "Last match is in row " & rowNumber
                Exit Sub
            End If
        End If
    Next rowNumber

    MsgBox "No match was found."
End Sub

This loop assumes row 1 is a header and data begins in row 2. The IsError check prevents an error value in a cell from causing a comparison error. It uses Value2, which reads the underlying value without VBA’s Date and Currency conversions. If the target column contains dates or numbers, choose the target’s data type deliberately and test with representative workbook values.

Common Problems and Edge Cases

  • No match: A failed Find returns Nothing, not row zero. Test for Nothing before using the result.
  • Partial match instead of exact match: Set LookAt:=xlWhole. The Find dialog’s previous setting is not a reliable substitute.
  • Wrong worksheet: Qualify ranges with a worksheet variable, as in ws.Columns("A"). Unqualified Range or Cells references can point to the active sheet, which may not be the intended one.
  • Wildcards in the target: Find treats asterisks and question marks as wildcard characters. To search for those characters literally, escape them with a tilde; a literal tilde must also be escaped. For a text target, a prepared string can be made with Replace(Replace(Replace(CStr(searchValue), "~", "~~"), "*", "~*"), "?", "~?").
  • Blanks as the target: Searching for an empty string is less clear than searching for a nonblank value because empty cells and formulas returning empty text are different situations. If blank means “no entered value,” use a loop with an explicit test such as Len(cellValue) = 0, and decide separately how formula cells should count.
  • Unexpected formula matches: Check whether the intended search is for a calculated result or formula text, then choose xlValues or xlFormulas accordingly.

Excel for Windows, Mac, and the Web

The examples are for desktop Excel for Microsoft 365 on Windows. VBA can also be used in desktop Excel for Mac, although the VBA editor, macro permissions, and some workbook features can differ. This basic Range.Find approach is generally portable because it uses standard Excel VBA object-model members; test macros in the actual workbook and platform where they will run.

Excel for the web does not run VBA macros. A workbook can be opened there, but this macro must be run in desktop Excel. If the task must run in a browser, consider Office Scripts where available, or use a worksheet formula or filter instead. These are alternatives rather than ways to execute VBA in Excel for the web.

Frequently Asked Questions

How do I find the last row containing a value in a specific column?
Set a Range to that column, call Find with SearchDirection:=xlPrevious and LookAt:=xlWhole, then read the returned cell’s Row property. Check that the result is not Nothing first.

How do I find the last row containing text anywhere in a row?
Search only the columns that define your records, or use a range covering those columns. Be aware that a broad search can match the text in a notes field rather than the record’s key field. If you need the last row where several conditions are true, use a loop and test each relevant column.

Can I find the last occurrence when the cells contain formulas?
Yes. Use LookIn:=xlValues to search formula results, or LookIn:=xlFormulas to search formula contents. Decide which one matches the question, because a displayed result and its underlying formula are not the same text.

Why does Find return Nothing even though I can see the value?
Check the search range, exact-versus-partial matching, formula result versus formula text, and whether the apparent value is actually formatted differently or contains extra spaces. Also specify the Find options explicitly so a previous search’s settings do not influence the macro.

Can I use this macro in Excel for the web?
No. Excel for the web does not execute VBA. Run the macro in desktop Excel, or choose a browser-compatible alternative such as a worksheet formula or, where available, Office Scripts.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.