Range.Find fails on ranges that are both hidden AND part of a filter

Question

I'm experiencing a peculiar problem in Excel 2003 where the Range.Find method fails when searching for a value in a cell that is both hidden and part of a filtered range.

To be clear, this is the method call in question:

Cells.Find(SearchString, LookIn:=xlFormulas, LookAt:=xlWhole)

• If the cell containing SearchString is merely hidden, Range.Find works.
• If the cell containing SearchString is merely part of a filtered range (but not hidden), Range.Find works.
• If the cell containing SearchString is both hidden (by a filter or otherwise), AND is part of a filtered range, Range.Find fails.

Many sources on various Excel sites and forums claim that specifying "LookIn:=xlFormulas" will force Range.Find to search within hidden cells. Though nonsensical, that seems to be true if SearchString is in a cell that is merely hidden. If the cell is both hidden and part of a filtered range, it fails.

Note that it doesn't matter if the cell is being hidden by the filter. For example, you can search for the heading of a filtered range (which will never be hidden by the filter itself), but if that heading happens to be in a column you've hidden, Range.Find will fail.

Is there any Excel method that will just reliably search cells without regard for whether or not they happen to be hidden and/or part of a filter?

Answer 1

More detail would be helpful

• for a 1D range you could use Match
• for 2D a variant array either looking through each element of the array, or applying MATCH to each column of the array

Samples below 1D

Sub D1()

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Range("C5:C100")
Dim StrTest As String
Dim X As Variant

StrTest = "Filtered"

X = Application.Match(StrTest, rng1, 0)
If IsError(X) Then
    MsgBox "no match"
Else
    MsgBox "Found in position " & X
    Set rng2 = rng1.Cells(X)
End If
End Sub

Sub D2()

2D

Dim X
Dim lngRow As Long
Dim lngCol As Long
Dim StrTest As String


X = Range("C5:D100").Value2
StrTest = "Filtered"

For lngRow = 1 To UBound(X, 1)
    For lngCol = 1 To UBound(X, 2)
        If X(lngRow, lngCol) = StrTest Then
         Set rng1 = [c5].Offset(lngRow - 1, lngCol - 1)
         MsgBox "Found in position " & rng1.Address
        End If
    Next
Next

End Sub