excelavon

VBA Find Row: Search a Column and Return the Match

VBA & macros · Updated November 3, 2026

VBA Find Row: Search a Column and Return the Match

To find a row in VBA, use Excel’s Range.Find method on the column or range you want to search, then read the matching cell’s Row property. For example, search column A for an exact value, check whether Find returned a cell, and use foundCell.Row to get its worksheet row number.

What “find row” means in VBA

People use “find a row” to mean a few different things: locate a value in a column and report the row number, find the first row that meets a condition, or identify the next blank row for new data. These tasks require different approaches. Range.Find is usually the clearest choice when you have a value to search for and want the row containing it.

The key distinction is between a cell’s position within the searched range and its worksheet row number. If you search A2:A100, the sixth matching position is not row 6; it is row 7. Using the found cell’s Row property avoids that conversion problem. It returns the actual row number on the worksheet, regardless of whether the search starts at row 1.

The examples below are for desktop Excel for Microsoft 365 on Windows. VBA macros do not run in Excel for the web. Excel for Mac supports VBA, though keyboard shortcuts for opening the editor can depend on the keyboard; Windows users can press Alt+F11, while Mac users can try Option+F11 (or Fn+Option+F11).

Basic example: find an exact value in a column

This macro searches column A of a worksheet named Data for the exact value “Mira Chen.” If it finds a match, it displays the row number. Replace the sheet name, column, and search value with those in your workbook.

Sub FindRowInColumnA()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim searchValue As String

    ' Set the worksheet and column to search.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Columns("A")
    searchValue = "Mira Chen"

    ' Specify Find options rather than relying on saved dialog settings.
    Set foundCell = searchRange.Find( _
        What:=searchValue, _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

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

Before running any macro, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z. To run the example, open the Visual Basic Editor, insert a standard module, paste the code, and run the procedure. The worksheet named Data must exist in the workbook containing the macro; otherwise, the line that sets ws will fail.

The search covers the whole of column A, including its header. If the header could contain the search value, narrow the range to data rows, such as ws.Range("A2:A1000"). This can also reduce unnecessary searching in a large sheet.

How the Find settings affect the result

Find has several options that determine what counts as a match. In the example, they are written out so the macro behaves predictably instead of inheriting settings from Excel’s Find dialog or a previous VBA search.

  • What is the value or text to locate.
  • LookIn:=xlValues searches cell values as Excel displays them. Use xlFormulas when you need to search the formula text itself.
  • LookAt:=xlWhole requires the entire cell contents to match. xlPart allows a match within the cell.
  • SearchOrder:=xlByRows searches across rows when the range contains more than one column. For a single column, it is still a clear, explicit choice.
  • SearchDirection:=xlNext searches forward; Excel wraps around to the start of the range when it reaches the end.
  • MatchCase:=False makes the search case-insensitive. Set it to True if capitalization must match.
  • SearchFormat:=False prevents formatting criteria from affecting the search.

The After argument sets where the search begins. It must be a single cell inside the search range. In this example, starting after the last cell makes the next forward search begin at the top of the range. The search can wrap around, so it is still important to check the returned cell rather than assume that a result means the value was found.

Exact matches, partial matches, and wildcards

For an exact match, use LookAt:=xlWhole. Searching for “Mira” will not match a cell containing “Mira Chen.” If you want the shorter text to match inside a longer cell, use LookAt:=xlPart. This is useful for names, product descriptions, or codes with variable suffixes, but it can return an unintended result when several cells contain similar text.

You can also use Excel’s standard search wildcards. For example, with column A as the search range, What:="*Chen*" and LookAt:=xlPart can locate text containing “Chen.” An asterisk represents any number of characters; a question mark represents one character. If the text you need to find contains a literal asterisk or question mark, prefix that character with a tilde in the search string so Excel treats it as a character rather than a wildcard.

For numbers, dates, and values returned by formulas, choose LookIn deliberately. A date displayed as “Nov 2” may be stored as a serial number, and a formula cell contains both a formula and a calculated value. Searching values is often what a user expects when looking for what appears in the cell, while xlFormulas is appropriate when the formula expression itself is the target.

Search a smaller range or a different column

Searching a whole column is convenient, but a defined data range makes the intended scope obvious and excludes headers. If the IDs are in column C from row 2 through row 500, set searchRange to that range and leave the rest of the method unchanged:

Set searchRange = ws.Range("C2:C500")

To search a different column, change the range, not the meaning of the returned row. If the match is in C27, foundCell.Row returns 27. If you instead need the column number, use foundCell.Column; that returns 3 for column C.

When the final data row changes regularly, you can calculate it before setting the range. For example, ws.Cells(ws.Rows.Count, "C").End(xlUp).Row finds the last non-empty cell in column C when moving upward from the bottom of the sheet. This is a common way to set a range from row 2 to the last used row, but it assumes the column is populated for every relevant record. If there are gaps or the key column can be blank, choose a more reliable column or another way to establish the data boundary.

Find every matching row

Find returns one match, not a collection of all matches. To process duplicates, save the address of the first matching cell, then use FindNext until the search loops back to that address. The loop below prints each matching row number in the Immediate window in the Visual Basic Editor.

Sub PrintAllMatchingRows()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim nextCell As Range
    Dim firstAddress As String

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A1000")

    Set foundCell = searchRange.Find( _
        What:="Mira Chen", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If Not foundCell Is Nothing Then
        firstAddress = foundCell.Address

        Do
            Debug.Print foundCell.Row
            Set nextCell = searchRange.FindNext(After:=foundCell)
            If nextCell Is Nothing Then Exit Do
            If nextCell.Address = firstAddress Then Exit Do
            Set foundCell = nextCell
        Loop
    End If
End Sub

Open the Immediate window with Ctrl+G in the Windows Visual Basic Editor. The loop stops when it reaches the first match again, preventing an endless cycle as the search wraps around. It also checks for a missing result before trying to read an address or row.

Common errors and how to avoid them

  • Object variable not set: Find returns Nothing when no match exists. Always test If foundCell Is Nothing Then before reading foundCell.Row.
  • The wrong row is reported: foundCell.Row is the worksheet row. Do not treat the match’s position in a range such as A2:A100 as its row number.
  • A partial or unexpected match appears: Check whether the code uses xlPart, wildcards, or a range that includes a header. Use xlWhole for an exact cell match.
  • Results change after using Find manually: Excel’s Find settings can persist between searches. Pass the relevant arguments in VBA each time instead of depending on dialog settings.
  • The macro searches the wrong workbook or sheet: ThisWorkbook means the workbook containing the VBA code. If the target data is in a different workbook, set the worksheet from that workbook explicitly.
  • No result for a value that looks identical: Check for leading or trailing spaces, different data types, formula results, and hidden characters. A number stored as text may not behave like a numeric value in every comparison.

When a worksheet formula is enough

If you only need a row number in a cell and do not need a macro, the MATCH worksheet function may be simpler. With names in A2:A100, “Mira Chen” in A7, and an exact-match formula, =MATCH("Mira Chen",A2:A100,0)+ROW(A2)-1 returns 7. The addition converts the position within the range into the actual worksheet row. If the value is absent, MATCH returns #N/A.

Use VBA when the result should trigger another action, such as selecting a record, copying a row, or updating a report. Use a worksheet formula when the row number should remain visible and update as the data changes. Newer Excel versions also have lookup functions, but a formula is not necessary just to make VBA’s Find return a row.

Frequently Asked Questions

How do I get the row number after VBA Find?
Store the result of Find in a Range variable, confirm it is not Nothing, and read foundCell.Row.

Does VBA Find return the first match?
It returns the next match from the specified starting point and search direction, wrapping around the range if necessary. Set After and the other search options explicitly when the order matters.

How do I find a row when the value is in column B?
Set the search range to column B, such as ws.Columns("B"), or to the relevant data rows, such as ws.Range("B2:B500"). The matching cell’s Row property still gives the worksheet row.

How do I find all rows with the same value?
Use Find for the first match and repeatedly call FindNext. Record the first match’s address and stop when the search returns to it, as in the example above.

Can I run VBA in Excel for the web?
No. Excel for the web can open a workbook containing macros, but it cannot run, create, or edit VBA macros. Use the Excel desktop application for VBA.

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.