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 <robert@roberttodar.com>
''
Function Find(ByRef searchRange As Range, ByVal what As Variant) 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 <robert@roberttodar.com>
''
Public Function FindFast(searchRange As Range, what As Variant) 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
