Perform a find on hidden cells

Question

I have a computed range of values in a hidden column which I use for a dropdown box. To figure out which value the user has selected, I try to run a Find on that range, but for some reason Excel won't return the cell corresponding with their selection so long as the column is hidden.

How can I get Find working on cells in the hidden range. Remember - I'm searching cell calculated values, not formulas.

The following does not work:

Set inserted = Range("RDS_Event_IDs").Find(Range("SelectedEvent"), , xlValues, xlWhole)

so long as cells in Range("RDS_Event_IDs") is hidden.

Because the solution has to work in general situations, where some or all of the the range being searched might be hidden, and the entire sheet might be searched, it isn't feasible to programmatically un-hide all affected rows and columns and then re-hide the ones that were previously hidden.

Answer 1

According to Andy Pope (and he's never wrong) Find only works on hidden cells if you're using xlFormulas. Perhaps a Match instead?

Set inserted = Cells(Application.WorksheetFunction.Match("SelectedEvent", Range("RDS_Event_IDs"), 0), Range("RDS_Event_IDs").Column)

Answer 2

Is really necesary do it inside a macro, would be easier use match:

=MATCH(G9;H9:H16;0)

G9 : Cell of the DropDownBox

H9:H16 : Your range

0 : for exact match

It returns the index inside the array

Answer 3

Functional Approach

Using Doug Glancy's answer, it would be nice to put that in a function for reusability.

''
' Find a range using `WorksheetFunction.Match()`. This alternative works well
' for finding range in hidden cells, and is not case sensitive.
'
' Created this solution based on answer on Stack Overflow @see https://stackoverflow.com/a/6298404/8309643
'
' @author Robert Todar <[email protected]>
''
Function Find(ByRef searchRange As Range, ByVal what As String) As Range
    Set Find = Cells(Application.WorksheetFunction.Match(what, searchRange, 0), searchRange.Column)
End Function

Another alternative for searching for a range is to get an array from the range and loop that. Again, putting this in a function makes it easy to re-use!

''
' Finds a range based on it's value.
' This works faster than `Range.Find()` as it loops an array instead of cells.
' This also works for hidden cells where `Range.Find` does not.
'
' Note, this looks for first match, and is case sensitive by defaut, unless
' Option Match Case is used at the top of the module it is stored in.
'
' @author Robert Todar <[email protected]>
''
Public Function FindFast(searchRange As Range, what As String) As Range
    ' Get data from range into an Array. Looping Arrays is much
    ' faster than looping cells.
    Dim data As Variant
    data = searchRange.Value
    
    ' Loop every row in the array.
    Dim rowIndex As Long
    For rowIndex = LBound(data, 1) To UBound(data, 1)
        
        ' Loop every column in the array.
        Dim columnIndex As Long
        For columnIndex = LBound(data, 2) To UBound(data, 2)
        
            ' If current row/column matches the correct value then return the range.
            If data(rowIndex, columnIndex) Like what Then
                Set FindFast = searchRange.Cells(rowIndex, columnIndex)
                Exit Function
            End If
        Next columnIndex
    Next rowIndex
    
    ' If the range is not found then `Nothing` is returned.
    Set FindFast = Nothing
End Function