I'm trying to find the 1st and last rows on a sheet that isnt active (I plan on it being hidden at some point). The following code works fine, if the sheet is active, but otherwise isn't working.
Public Type rowRange
'This Type must go with rangeFinder
firstRw As Long
lastRw As Long
End Type
Function rangeFinder(searchTerm As String, searchCol As Integer, Optional lastRw = -1) As rowRange
'the TYPE at the top of this module must go with this function for it to work
'lastRw will be last entry in the column if not specified
Dim thisRange As rowRange
On Error GoTo NoEntryFound
If lastRw < 2 Then lastRw = ThisWorkbook.Sheets("WorkSheet").Cells(1, 2).End(xlDown).Row
With ThisWorkbook.Sheets("WorkSheet").Range(Cells(1, searchCol), Cells(lastRw, searchCol))
thisRange.firstRw = .Find(What:=searchTerm, After:=.Rows(1), LookIn:=xlFormulas2, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Row
thisRange.lastRw = .Find(What:=searchTerm, After:=.Rows(1), LookIn:=xlFormulas2, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:= _
False, SearchFormat:=False).Row
End With
rangeFinder = thisRange
Exit Function
NoEntryFound:
thisRange.firstRw = -1
thisRange.lastRw = -1
rangeFinder = thisRange
End Function