VBA Find a Row With a Specific Value in an Excel Sheet

To find a row with a specific value in Excel VBA, use the Find method on the relevant range, then read the matched cell’s Row property. For example, search column A for an exact value and use foundCell.Row to get its worksheet row number.

Basic VBA example using Find

This macro searches cells A2:A100 on a worksheet named Data for the exact value North. It reports the row number if it finds a match and handles the case where the value is absent.

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

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")
    searchValue = "North"

    ' Search for an exact match among cell values.
    Set foundCell = searchRange.Find( _
        What:=searchValue, _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        MatchByte:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "Value not found."
    Else
        MsgBox "Found on worksheet row " & foundCell.Row
    End If
End Sub

Change the worksheet name, range, and search value to match your workbook. The example searches the values Excel calculates or displays, rather than formula text, and ignores letter case. Because the search range begins at row 2, a match in its first cell will correctly return worksheet row 2. The Row property is an absolute worksheet row number, not a position counted from the start of the search range.

What Find does and why the arguments matter

Range.Find searches a specified range and returns a Range object for the first match it encounters. It does not return a row number by itself: assign the result to a Range variable, check whether it is Nothing, and then read its Row property. That check matters because asking for foundCell.Row when no cell was found causes a run-time error.

The method’s options determine what counts as a match and how the search proceeds. The example states each important option explicitly instead of relying on whichever settings were last used in Excel’s Find dialog. Excel can retain Find settings between searches, so specifying them makes a macro more predictable.

What is the value or text to search for.
LookIn:=xlValues searches cell values, including the results of formulas. Use xlFormulas when you want to search the formula text itself.
LookAt:=xlWhole requires the whole cell content to match. xlPart accepts a match within longer text.
SearchDirection:=xlNext searches forward. SearchOrder controls the order in which cells are examined.
MatchCase:=False makes the search case-insensitive; set it to True for a case-sensitive search.

The After argument must be one cell inside the range. The search begins after that cell and eventually wraps around to it. In the example, using the range’s last cell as After makes the first cell in the range the first one examined. This detail is easy to overlook when a search appears to skip an expected match.

Adapt the search to your worksheet

Search only the column or block that contains the lookup values. If product codes are in column D, for example, set the search range to a suitable part of column D, such as D2:D500. When the result is found, foundCell.Row identifies the row across the worksheet, so you can use that row number to read related values from other columns.

For instance, if column D contains product codes and column E contains descriptions, you can use the matched row to refer to the corresponding description cell. The row number is not the same as the match’s ordinal position in the range: if the first match is in D37, foundCell.Row is 37, even if D37 is the second cell in a smaller range such as D36:D40.

Use ThisWorkbook when the target sheet is in the workbook containing the macro. If the macro is stored in a personal macro workbook or another file, the target workbook may instead need to be referenced explicitly. Confirm that the sheet name is spelled exactly as it appears on its tab; an incorrect name causes an error before the search runs.

Exact matches, partial matches, and cell values

For an exact match, keep LookAt:=xlWhole. Searching for North will then match a cell containing North, but not Northern Region. Set LookAt:=xlPart when finding a substring is intended; that same search could then match Northern Region. Pick the setting based on the data, not just on what seems convenient, because partial matches can return an unintended row.

Text and numbers can also behave differently. A cell containing the number 42 is not always equivalent to a cell containing the text “42”, even if their displayed contents look alike. If a lookup unexpectedly fails, check the underlying cell type, leading or trailing spaces, and whether the cell contains a formula. Formatting a number to display with leading zeroes does not necessarily turn it into text: a cell displaying 0042 may still hold the numeric value 42.

For formula cells, LookIn:=xlValues searches the calculated result, while LookIn:=xlFormulas searches the formula as written. For example, if a formula displays North, a value search can find North; a formula search is useful when looking for text that appears in the formula expression. If the search value includes Excel wildcard characters such as an asterisk or question mark, remember that Find can interpret them as wildcards. Escape a character when you need to search for it literally.

Find the first match or every matching row

The basic example returns the first match encountered in the range’s search order. It does not guarantee that the matching row is unique. If North appears in A5, A18, and A42, a single Find call returns only one of those cells. That is suitable when the value is unique or when any first match is acceptable, but it is not enough when every occurrence matters.

To visit all matches, save the first match’s address and call FindNext repeatedly until the search comes back to that address. This example prints each matching worksheet row in the Immediate window in the VBA editor:

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

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

    ' Find the first exact match.
    Set foundCell = searchRange.Find( _
        What:="North", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        MatchByte:=False, _
        SearchFormat:=False)

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

Open the Immediate window in the VBA editor with Ctrl+G on Windows to see the printed row numbers. The loop stops when it returns to the original match, preventing an endless cycle through a range containing matches. If you want to store results instead, replace the print statement with code that writes row numbers to an array, collection, or worksheet. Test that logic on a copy if it writes into the workbook.

How to add and run the macro

On Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. Choose Insert > Module, paste the macro into the module, and update its sheet name and range. Return to Excel, then run it from the editor with the cursor inside the procedure by pressing F5, or run it from Developer > Macros.

If the Developer tab is not visible, it can be enabled through File > Options > Customize Ribbon by selecting Developer. On Mac, open the Visual Basic Editor with Option+F11; some keyboards require Fn+Option+F11. The VBA editor and macro support are available in desktop Excel for Mac, though keyboard behavior can depend on the keyboard and macOS settings. Excel for the web can open macro-enabled workbooks but cannot create, edit, or run VBA macros; use desktop Excel for these steps.

Save a workbook that contains VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the macro. Save a separate copy before running a macro, especially before adapting it to write or delete data: macro actions cannot be undone with Ctrl+Z on Windows. The examples above only display or print results, but the same search pattern is often expanded to edit cells, so verify the range and target sheet before making changes.

Common problems and practical checks

If the macro reports no match even though the value seems present, check the range first. A value in A101 will not be found by a search limited to A2:A100. Next, check whether the value is stored as text or as a number, whether spaces are present, and whether LookAt should be xlWhole or xlPart. A formula’s displayed result may also differ from the formula text, which is why the LookIn choice matters.

If the procedure stops on the worksheet assignment, verify the workbook reference and sheet name.
If it stops when reading Row, make sure the code checks for Nothing first.
If it finds a surprising match, confirm the search range, case setting, exact-versus-partial setting, and whether the first match is the one you wanted.
If the search behaves differently after using Excel’s Find dialog, set all relevant Find arguments explicitly in VBA.

Find searches the specified cells, including cells hidden by a filter or hidden rows; it is not limited to visible results. If you need to search only visible cells, that requires additional logic to test visibility rather than assuming Find will skip hidden rows. Likewise, foundCell.Row is a worksheet row number. In an Excel table, the table’s first data row may not be row 1, so do not treat a worksheet row number as a table-relative item number without converting it.

When a loop may be a better alternative

For a small, straightforward range, a For loop can be easier to customize than Find, especially if the condition involves several columns or a rule beyond equality. A loop lets you test each cell’s value and apply your own comparisons. However, a loop requires more code to handle types, case, and text matching consistently. Find is often a concise choice for locating a value in one column; use a loop when the decision depends on multiple fields or custom conditions.

Another option is to use a worksheet lookup formula when the row result is needed in a cell rather than by a macro. The appropriate formula depends on the Excel version and whether you want a value or a position. A formula does not replace VBA when subsequent code must act on a Range object, but it can be simpler for a worksheet-only lookup. Choose based on where the result is needed: a macro can use the matched cell directly, while a formula can update as worksheet data changes.

Frequently Asked Questions

How do I return the entire row after finding a value?
After Find returns a cell, use its row number to refer to the worksheet row. For example, ws.Rows(foundCell.Row) refers to the whole row. Check that foundCell is not Nothing before using it.

How do I find a value in a different column?
Change the search range, such as from A2:A100 to B2:B100. The returned Row still gives the worksheet row, so it can identify related data in the same row.

How can I make the VBA search case-sensitive?
Set MatchCase:=True in the Find call. With this setting, text such as North and NORTH is treated as different for the search.

Does Find return the first duplicate?
It returns the first match encountered according to the search order and starting point, not necessarily the first duplicate from the top of the worksheet. Set the search range and After argument deliberately; use FindNext to process every match.

Can I use this macro in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in desktop Excel for Windows or Mac to use the VBA editor and run the procedure.

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.