VBA Excel find functions: find all cells in range or worksheet that contain a specific value

The function below returns a (non-contiguous) range object containing all the cells in a given range or within the used range of a worksheet that contain the specified search string (the “needle” to be found in the “haystack”). Returns nothing if the search string was not found, or neither a range object nor a worksheet object was passed.

Table of Contents

Function
Parameters
Usage example

Function

VBA
Function FindAll(findString As String, Optional searchRng As Range, Optional searchSheet As Worksheet) As Range

    Dim firstFound As String
    Dim foundCell As Range, lastCell As Range, rng As Range
    Dim objSearch As Object
    
    On Error Resume Next
    If searchRng Is Nothing And searchSheet Is Nothing Then Exit Function
    
    If Not searchRng Is Nothing Then Set objSearch = searchRng Else Set objSearch = searchSheet.UsedRange
        
    Set lastCell = objSearch.Cells(objSearch.Cells.Count)
    Set foundCell = objSearch.Find(what:=findString, after:=lastCell)
    
    If Not foundCell Is Nothing Then firstFound = foundCell.Address Else Exit Function
    
    Set rng = foundCell
    
    Do Until foundCell Is Nothing
        Set foundCell = objSearch.FindNext(after:=foundCell)
        Set rng = Union(rng, foundCell)
        If foundCell.Address = firstFound Then Exit Do
    Loop
    
    Set FindAll = rng
      
End Function

Parameters

Name	Optional/ Required	Data type	Description
findString	Required	String	The search string to be found.
searchRng	Optional	Range	The cell range in which the search string is to be found.
searchSheet	Optional	Worksheet	The worksheet in which the search string is to be found within the used cell range.

Usage example

VBA
Sub TestFindAll()

    Dim rng As Range
    
    ' search all cells in range
    Set rng = FindAll("findMe", Selection) ' replace "findMe" with the value to be found
    If Not rng Is Nothing Then Debug.Print rng.Address
    
    ' search all cells in used range of worksheet
    Set rng = FindAll("findMe", , ActiveSheet) ' replace "findMe" with the value to be found
    If Not rng Is Nothing Then Debug.Print rng.Address

End Sub