0

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
David D.
  • 25
  • 5
  • 1
    `With ThisWorkbook.Sheets("WorkSheet").Range(ThisWorkbook.Sheets("WorkSheet").Cells(1, searchCol), ThisWorkbook.Sheets("WorkSheet").Cells(lastRw, searchCol))` – Scott Craner May 17 '23 at 16:10

0 Answers0