Find Range Function

[smuzoen]

I have used this so much I cannot do without it - it is a quick method to find all the ranges within a range than containing a value. The function is

Function Find_Range(Find_Item As Variant, _
    Search_Range As Range, _
    Optional LookIn As Variant, _
    Optional LookAt As Variant, _
    Optional MatchCase As Boolean) As Range
     
    Dim c As Range
    If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
    If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
    If IsMissing(MatchCase) Then MatchCase = False
     
    With Search_Range
        Set c = .Find( _
        What:=Find_Item, _
        LookIn:=LookIn, _
        LookAt:=LookAt, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=MatchCase, _
        SearchFormat:=False)
        If Not c Is Nothing Then
            Set Find_Range = c
            firstAddress = c.Address
            Do
                Set Find_Range = Union(Find_Range, c)
                Set c = .FindNext(c)
            Loop While c.Address <> firstAddress
        End If
    End With
     
End Function

This can be called with

Set myRange = Find_Range(myVar, Worksheets("Sheet1").Columns("A"), xlValues, xlWhole)

Then simple matter of iterating through myRange to get all the ranges with the value. Could not live without this great function. Very robust.

[shg]

You can assign default values to the arguments in the signature:

Public Function FindRange(vWhat As Variant, _
                          rSearch As Range, _
                          Optional LookIn As XlFindLookIn = xlValues, _
                          Optional LookAt As XlLookAt = xlPart, _
                          Optional bMatchCase As Boolean = False) As Range

    ' FindRange by Aaron Blood on ozgrid.com/forum

In addition, and notwithstanding that it appears as an example in Help, this line makes no sense:

          Loop While Not c Is Nothing And c.Address <> firstAddress

In the code here, c cannot be Nothing (that's already been tested), and if it were, c.Address would generate a runtime error.

[GeneralDisarray]

I've been using a similar loop but didn't know about the UNION trick...that kicks a$$

[smuzoen]

@shg you are dead right - my bad - it should be

Do
                Set Find_Range = Union(Find_Range, c)
                Set c = .FindNext(c)
            Loop While c.Address <> firstAddress