Find Text in Hidden Cells

Hi! This is my first ever post, so bear with me as I find my feet.

Excel's Find functionality is getting better as they release new versions, but there is still nothing that will allow you to search through hidden cells for text. I have included a snapshot of code that adds this functionality to the Find function.

To use it, copy the code into a module and type "FindThis" onto a sheet, hide the row or column and run the FindhiddenText routine. This code can be customised quite a lot e.g. Find All, Find on Multiple sheets etc.

Option Explicit

Public Function AdvancedFind(rToSearch As Range, _
sToFind As String) As Range

Dim rFound As Range
Dim rHiddenCols As Range
Dim rHiddenRows As Range

'unhide cols/rows
Set rHiddenCols = UnHideColumns(rToSearch)
Set rHiddenRows = UnHideRows(rToSearch)

Set rFound = rToSearch.Find(what:=sToFind)

'hide cols/rows
Call HideRowsAndColumns(rHiddenCols, rHiddenRows)

Set AdvancedFind = rFound

End Function

Sub HideRowsAndColumns(rHiddenCols As Range, _
rHiddenRows As Range)

Dim r As Range

    If Not rHiddenCols Is Nothing Then
        For Each r In rHiddenCols.Columns
            r.Columns.Hidden = True
        Next r
    End If
        
    If Not rHiddenRows Is Nothing Then
        rHiddenRows.Rows.Hidden = True
    End If
    

End Sub

Sub FindHiddenText()

Dim r As Range
Dim text As String

text = "FindThis"

Set r = AdvancedFind(ActiveSheet.Cells, text)

'display message
If Not r Is Nothing Then
    MsgBox "Found " & text & " at " & r.Address
Else
    MsgBox text & " not found"
End If

End Sub

Function UnHideColumns(rToSearch As Range) As Range

'''''''''''''''''''''
Dim c As Range
Dim rHiddenCols As Range

Set rHiddenCols = Nothing

For Each c In rToSearch.Columns
    If c.Hidden = True Then
        'unhide col
        c.Hidden = False
        
        'add to union range
        If rHiddenCols Is Nothing Then
            Set rHiddenCols = c
        Else
            Set rHiddenCols = Union(rHiddenCols, c)
        End If
    End If
Next c

Set UnHideColumns = rHiddenCols




End Function


Function UnHideRows(rToSearch As Range) As Range

'''''''''''''''''''''
Dim c As Range
Dim rHiddenRows As Range

Set rHiddenRows = Nothing

For Each c In rToSearch.Rows
    If c.Hidden = True Then
        'unhide col
        
        'add to union range
        If rHiddenRows Is Nothing Then
            Set rHiddenRows = c
        Else
            Set rHiddenRows = Union(rHiddenRows, c)
        End If
    
    
    End If
Next c

If Not rHiddenRows Is Nothing Then
    rHiddenRows.Rows.Hidden = False
End If


Set UnHideRows = rHiddenRows


End Function