Excel VBA: Find a Row with Specific Text in a Column

To find a row containing specific text in Excel VBA, use the range’s Find method, then read the matching cell’s Row property. For example, search column A for an exact match and use foundCell.Row to get the row number.

How the VBA Find method works

Range.Find searches a specified range and returns a Range object for the first matching cell. It does not return a row number directly: the match is a cell, and its Row property supplies the worksheet row. If no match exists, the result is Nothing, so check for that before using the result.

This example searches cells A2:A100 on a worksheet named Data for the exact value North. Change the worksheet, range, and search text to fit your workbook.

Sub FindSpecificTextRow()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim searchText As String

    ' Use the worksheet in the workbook containing this macro.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")
    searchText = "North"

    With searchRange
        Set foundCell = .Find(What:=searchText, _
            After:=.Cells(.Cells.Count), _
            LookIn:=xlValues, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            MatchByte:=False, _
            SearchFormat:=False)
    End With

    If foundCell Is Nothing Then
        MsgBox "Text was not found."
    Else
        MsgBox "Found in row " & foundCell.Row
    End If
End Sub

The code uses a fully qualified worksheet and range, so the search does not depend on whichever sheet happens to be active. ThisWorkbook means the workbook where the macro is stored. If the macro is in a different workbook from the data, set ws to the intended workbook and worksheet explicitly.

Choose exact matching or partial matching

The LookAt argument controls whether the whole cell must match or whether the text can appear inside a longer cell value. With LookAt:=xlWhole, a search for North matches North, but not Northern or North Region. This is usually the right choice when searching for a category, ID, or complete label.

Use LookAt:=xlPart when the search term can appear anywhere within a cell. For example, searching for North can then match North Region. Partial matching can produce multiple plausible results, so decide what your macro should do if the first match is not the intended one. The setting applies to the entire search; it is not a separate wildcard or regular-expression mode.

Case sensitivity is controlled separately. MatchCase:=False treats North and NORTH as a match, while MatchCase:=True distinguishes them. Exact matching does not ignore extra spaces: a cell containing North followed by a space may not match North as a whole. If imported data may contain leading or trailing spaces, clean the source values or account for that in the search logic.

Set every important search option

Excel remembers several Find settings from earlier searches made in the user interface or by VBA. If a macro leaves an argument unspecified, an earlier search can affect its behavior. That is why the example supplies options such as LookIn, LookAt, and SearchDirection instead of relying on Excel’s current Find settings.

LookIn:=xlValues searches the evaluated cell values. Use xlFormulas when you need to search formula text itself, such as a formula containing a particular reference or phrase.
LookAt:=xlWhole requires a complete cell match; xlPart allows the term to occur within the cell.
SearchDirection:=xlNext searches forward. Find wraps around to the start of its range when it reaches the end.
SearchOrder:=xlByRows specifies row order. In a single-column range it has little practical effect, but stating it makes the intended behavior clear.
SearchFormat:=False prevents a saved formatting criterion from changing the search. MatchByte is relevant to double-byte language settings.

The After argument specifies the cell after which Find begins. The example passes the range’s last cell, so the next search position is its first cell. Because Find wraps, this searches the full range rather than skipping the first cell.

Find in a whole column or a changing data range

A fixed range such as A2:A100 is simple, but it can miss records added below row 100. Searching the entire column with ws.Columns("A") includes the header and every worksheet row, which can be slower than searching only the data area and may return a header match. For a growing list, calculate the last used row in the relevant column and build the search range from row 2 through that row.

For example, with the worksheet stored in ws, this expression finds the last non-empty cell in column A:

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

Declare lastRow as a Long before using that statement. If row 1 contains headers and the last used row is at least 2, set the range to A2 through A and lastRow. Check that there is data below the header first: when the column has no data, the last-row calculation can point to the header row, leaving no valid data range to search. Also consider what “last used” means for your sheet; this method finds the last non-empty cell in column A, not the last populated row across every column.

Return the row number or use the matching cell

Once Find succeeds, foundCell.Row gives the worksheet row number. For example, if the match is in A17, the result is 17, even if the search range started at A2. The row property is an absolute worksheet row, not a position within the search range.

You can also use the returned cell to read or update related data. If the match is in column A and the record’s status is in column C on the same row, refer to ws.Cells(foundCell.Row, "C").Value. That is often more useful than selecting the cell or activating the sheet. Avoid relying on Selection or ActiveCell when the macro can work directly with the returned range; selections can change and make code harder to maintain.

Find every row when values repeat

Find returns one match, not all matching rows. To process duplicates, save the address of the first match, use FindNext to continue, and stop when the search returns to that starting address. The address check matters because Find searches wrap around the range; without it, a loop can keep cycling forever.

Dim firstAddress As String

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

This fragment assumes the first Find has already run and that searchRange is the same range used for the search. Replace Debug.Print with the action you need, such as copying a value or recording row numbers. Keep the Find options explicit in the initial call; FindNext continues that search. If the macro changes the searched cells during the loop, results may be harder to predict, so collect the matching rows first when the action could alter the search area.

Common problems and practical checks

The macro finds the wrong sheet. Qualify the worksheet and workbook instead of using ActiveSheet implicitly. Check that the sheet name in the code matches the tab name exactly.
It misses a match that looks identical. Check for extra spaces, nonbreaking spaces, different data types, and whether the text is displayed by a formula. Try xlPart only if partial matches are acceptable; it is not a general fix for every hidden-character issue.
It finds formula text rather than the displayed result. Choose xlValues to search calculated results or xlFormulas to search formulas. A cell that displays a formatted number may not have the same underlying value as a text string that looks like that number.
It reports an error when nothing matches. Test If foundCell Is Nothing Then before reading foundCell.Row or another property.
It skips part of the range or changes behavior between runs. Make sure After is inside the searched range and specify the Find options rather than inheriting settings from a prior search.

Before running a macro that changes workbook content, save a copy of the workbook. VBA actions cannot be undone with Ctrl+Z. Save macro-enabled workbooks as .xlsm if you need to retain the code. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, or use Developer > Visual Basic. On Mac, VBA is available in the desktop app, but keyboard shortcuts vary by keyboard; use the Tools menu’s macro or Visual Basic Editor command if needed. Excel for the web does not run VBA macros.

When a loop or worksheet formula may be better

Find is convenient for locating a match in a range, especially when you need the actual cell object. A simple VBA loop can be easier to customize if each cell needs different text-cleaning rules before comparison. It is also possible to use worksheet functions such as MATCH for a position, but that returns a relative position within a lookup range rather than a worksheet row number. For a range starting at row 2, convert that position to a row number carefully. In VBA, Find avoids that extra conversion and makes it straightforward to access neighboring cells.

For repeated searches across a very large dataset, consider the workbook’s structure as well as the search method. Restrict the search to the relevant column and data rows rather than scanning every cell on the sheet. If the requirement is to identify all rows meeting a condition and produce a refreshed report, a table filter or Power Query may suit the task better than a macro that searches one cell at a time.

Frequently Asked Questions

How do I get the row number after VBA finds text?
Store the Find result in a Range variable, check that it is not Nothing, and use its Row property, as in foundCell.Row.

How do I find text anywhere inside a cell?
Set LookAt:=xlPart in the Find call. Use xlWhole instead when the entire cell must equal the search text.

Does VBA Find distinguish uppercase and lowercase?
Only when you request it. Set MatchCase:=True for case-sensitive searches or MatchCase:=False to ignore letter case.

How do I find all rows containing the same text?
Use Find for the first result, remember its address, and continue with FindNext until the search returns to that address. This prevents the wraparound search from looping indefinitely.

Can I run this macro in Excel for the web?
No. Excel for the web can open a workbook containing VBA, but it does not run VBA macros. Use the desktop Excel application to run the code.

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.