Range.Find does not work in ranges that are hidden and part of the filter

Question

I had a particular problem in Excel 2003, where the Range.Find method fails to find a value in a cell that is hidden and part of the filtered range.

To be clear, this is a method call:

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

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

Many sources on various Excel sites and forums claim that specifying “LookIn: = xlFormulas” will make Range.Find look in hidden cells. Although this is pointless, it seems true if SearchString is in a cell that is simply hidden. If the cell is hidden and part of the filtered range, it fails.

Note that it doesn't matter if the cell is hidden by the filter. For example, you can search for the header of the filtered range (which will never be hidden by the filter itself), but if this header appears in the column that you have hidden, Range.Find will fail.

Is there any Excel method that will reliably check cells without considering whether they are hidden and / or part of the filter?

Answer 1

You can always create your own search function:

 Private Function FindAnywhere(target As Range, search As Variant) As Range Dim values() As Variant Dim row As Long Dim col As Long values = target.Value For row = LBound(values, 1) To UBound(values, 1) For col = LBound(values, 2) To UBound(values, 2) If values(row, col) = search Then Set FindAnywhere = target.Cells(row, col) Exit Function End If Next col Next row End Function 

The only caveat is that if you use (or intend to use) FindPrevious or FindNext, you will have to keep track of areas of an already found range.

Answer 2

More details would be helpful.

• for 1D range you can use Match
• for 2D, a variant array that scans each element of the array or applies 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