excelavon

Excel VBA: Find All Cells With a Value

VBA & macros · Updated November 3, 2026

Excel VBA: Find All Cells With a Value

To find every cell that matches a particular value in Excel VBA, use the Range.Find method and repeat the search with FindNext until it returns to the first match. To list every nonblank cell instead, loop through the range and test each cell’s value; the right approach depends on whether “with value” means a particular match or any populated cell.

What “find all cells with a value” can mean

There are two common tasks behind this question. You might want to find every occurrence of a specific item, such as the text North, the number 25, or a date. Or you might want to locate every cell that is not blank, regardless of what it contains. These are different searches: Find is useful for matching a chosen item, while a loop is straightforward for checking every cell for content.

The examples below target a defined range on the active worksheet. Change that range to fit your data, and consider qualifying it with a worksheet name if the macro should always search the same sheet. Before running any macro, save a copy of the workbook: VBA changes cannot be undone with Ctrl+Z.

Find every exact match with Find and FindNext

This macro searches A1:D100 for the exact text North, without distinguishing uppercase and lowercase. It prints each matching cell’s address and value in the Visual Basic Editor’s Immediate window. In the editor, open that window with Ctrl+G on Windows.

Sub FindAllExactMatches()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String
    Dim searchValue As Variant

    ' Set the worksheet, search area, and item to find.
    Set ws = ActiveSheet
    Set searchRange = ws.Range("A1:D100")
    searchValue = "North"

    ' Find a whole-cell match among displayed values.
    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
        Debug.Print "No matches found."
        Exit Sub
    End If

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

To search for a different item, change searchValue. For example, assign it the number 25 to look for that number, or assign it a date value. Use a date that Excel recognizes as a date, rather than assuming that a date-looking text string is stored the same way. Keep data types consistent when comparing numbers and text that looks like a number.

How the search loop avoids missing matches

Find returns one matching cell, not a collection of every match. The macro records that cell’s address, then calls FindNext to continue searching the same range. Excel’s search wraps around to the beginning after reaching the end, so the loop stops when it encounters the original address again. Without this stopping check, a loop can repeat indefinitely.

If no cell matches, Find returns Nothing. The If foundCell Is Nothing check handles that case before the code tries to read an address. The later check after FindNext also avoids attempting to use an address if no next match is returned.

The example uses LookAt:=xlWhole, meaning the entire cell content must match. If the target is North, a cell containing Northwest will not match. Change this argument to xlPart to find cells containing the target as part of a longer entry. For instance, a partial search for North can also match Northwest.

Find every nonblank cell, regardless of its contents

If you mean “show me all cells that contain anything,” a loop can check each cell directly. This example scans A1:D100 and prints populated cells to the Immediate window. It includes numbers, text, dates, error values, and formulas, including formulas whose displayed result is an empty string.

Sub ListNonblankCells()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim cell As Range

    ' Set the worksheet and area to inspect.
    Set ws = ActiveSheet
    Set searchRange = ws.Range("A1:D100")

    ' IsEmpty is False for a cell containing a formula.
    For Each cell In searchRange.Cells
        If Not IsEmpty(cell.Value2) Then
            Debug.Print cell.Address(External:=True), cell.Value
        End If
    Next cell
End Sub

There is an important distinction between an empty cell and a formula that displays nothing. A cell containing =IF(A1=1,"","Ready") is not truly empty: it contains a formula, even when its current result is an empty string. The loop above includes it. If you want only cells with a visible, nonempty result, use a different test and decide how to handle error values before applying text functions to cell contents.

Choose the range and where results appear

A fixed range such as A1:D100 is easy to understand and limits the search to the intended data. If the data grows, update the range or define it from a table or another known boundary. Searching an entire worksheet cell by cell can be slow, especially when repeated for many sheets or thousands of rows; restricting the search area is usually more efficient.

The examples write results to the Immediate window, which is useful for inspection but not a permanent report. You can instead write each address to a results column or collect matching cells for later processing. If a macro changes cell formatting, values, or other workbook content, make a backup first and clearly separate that action from the search itself. A search macro does not need to select each result just to read its address or value.

  • Use a specific range when the data area is known and small.
  • Use a worksheet-qualified range when the macro must search a particular sheet rather than whichever sheet is active.
  • Choose whether results should be inspected in the Immediate window, written to a report, or used by further code.

Find settings, formulas, and partial matches

Excel remembers several Find dialog settings, and those settings can affect VBA searches if the code leaves arguments unspecified. The example explicitly sets the search location, whole-cell versus partial matching, search order, direction, case sensitivity, and formatting behavior. That makes the result more predictable than relying on the last settings used in the Find dialog.

LookIn:=xlValues searches cell values, including the calculated results of formulas. If you need to find formula text itself—for example, formulas containing a particular reference—use LookIn:=xlFormulas instead. The choice matters: a formula such as =A1+B1 has formula text and a calculated value, and those are not the same thing to search.

MatchCase:=False treats uppercase and lowercase as equivalent for text searches. Set it to True if capitalization should matter. SearchOrder:=xlByRows searches across rows; use xlByColumns to search down columns. These settings change the order in which results are encountered, not which cells qualify.

Common problems and practical fixes

  • Only one result appears: Find returns one match. Use FindNext in a loop and stop when the search reaches its first result again.
  • A longer entry is also found: Check LookAt. Use xlWhole for an exact cell match or xlPart when the target may appear within other text.
  • The macro finds a formula instead of its displayed answer: Check LookIn. Use xlValues for calculated results and xlFormulas for formula contents.
  • The search seems inconsistent: Specify all relevant Find arguments. Excel can retain search choices from the Find dialog or earlier code.
  • No result appears even though the cell looks right: Check whether the stored content is text or a number, whether there are leading or trailing spaces, and whether the search range includes the cell.

Also note that a cell’s displayed format can differ from its underlying value. A date displayed as Nov 2, for example, is stored as a date serial number. Searching for a text string that merely resembles the displayed date may not behave like searching for the actual date value. If formatting or hidden characters are involved, inspect the cell’s formula bar and value before changing the search code.

Running VBA in Excel for Microsoft 365

On Windows, open the Visual Basic Editor with Alt+F11. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon, then choose Developer > Visual Basic. Paste the code into a standard module using Insert > Module, return to Excel, and run the macro from the editor or the Macro dialog. Save the workbook in a macro-enabled format such as .xlsm if it must retain VBA code.

On Mac, Excel also supports VBA, but shortcut keys can vary with the keyboard and function-key settings; Option+F11 or Fn+Option+F11 may open the editor. The Developer tab can be enabled through Excel > Settings (or Preferences in some releases) > Ribbon & Toolbar. Excel for the web can open a macro-enabled workbook but cannot create, run, or edit VBA macros; use the desktop app for these examples.

Frequently Asked Questions

How do I find all cells containing partial text in VBA?
Set LookAt:=xlPart in the Find call, then use the same FindNext loop and first-address stopping check. This returns cells whose contents include the search text, not just cells equal to it.

How can I make VBA find matches regardless of capitalization?
Set MatchCase:=False. This is the setting used in the exact-match example. To require matching capitalization, change it to True.

Can Find search every worksheet in a workbook?
Yes, by looping through the workbook’s worksheets and applying the search to each sheet’s chosen range. Track the first address separately for each range, because each worksheet has its own search cycle. Decide whether hidden sheets should be included before running such a macro.

Why does Find return only one cell?
Range.Find is designed to return the next match, not an array of all matches. Call FindNext repeatedly and stop when the first matching address is reached again to gather the complete set.

Can I run a VBA search in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the Excel desktop application for Windows or Mac to use these examples.

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.