Checking for last populated row in none active worksheet

[Thebatfink]

Hi, I'm using the code below to find the row number I want

With Worksheets("Compiled Sheets")
lastrow = Cells.Find(What:="*", searchorder:=xlByRows, _
 searchdirection:=xlPrevious).Row
End With

But it always gives me the row number of the first sheet in the work book..

I've tried

Workbooks("name").Sheets("name").Activate

Workbooks("name").Sheets("name").Select

etc etc but I can't see to make it look at the workbook and sheet I want. Everything else I ever do ussually works fine but obviously theres a reason its defaulting to this work sheet and not my intended one.. I just don't know what it is (I'm doing this from a different worksheets code window but I have tried it in a module and it still doesn't want to play ball)..

Any help would be greatly appreciated.

Thanks
Batfink!

 

[VoG]

You are missing a dot

With Worksheets("Compiled Sheets")
lastrow = .Cells.Find(What:="*", searchorder:=xlByRows, _
 searchdirection:=xlPrevious).Row
End With

 

[Thebatfink]

Hi thanks for the reply. With the . I get an error on the lastrow line..

twfn = Thisworkbook.Name

With Workbooks(twfn).Sheets("My Sheet")
        
lastrow = .Cells.Find(What:="*", searchorder:=xlByRows, _
 searchdirection:=xlPrevious).Row
    
End With

What am I doing wrong here?

Thanks

 

[VoG]

This works without error and returns the correct value when I run it

Sub test()
Dim twfn As String, lastrow As Long
twfn = ThisWorkbook.Name

With Workbooks(twfn).Sheets("My Sheet")
        
lastrow = .Cells.Find(What:="*", searchorder:=xlByRows, _
 searchdirection:=xlPrevious).Row
    
End With
MsgBox lastrow
End Sub

 

[Thebatfink]

I'm putting that into a worksheet module (not the worksheet module of the worksheet I'm trying to find the last row) and its erroring for me..

Run-time error '91':
Object variable or with block variable not set

this is the code

Private Sub test()
Dim twfn As String
Dim lastrow As Long

twfn = ThisWorkbook.Name

With Workbooks(twfn).Sheets("My Sheet")
        
lastrow = .Cells.Find(What:="*", searchorder:=xlByRows, _
 searchdirection:=xlPrevious).Row
    
End With

MsgBox lastrow

End Sub

I am using Excel 2007 with .xlsm file format?

 

[VoG]

That code should go in a regular module, not a sheet's class module.

 

[Thebatfink]

Oh no, I'm sorry. I've just tried it in a module and it still didn't work. I've just realised the first time its looped through the sheet is blank, but subsequent passes aren't hence checking the row number. Its erroring because the sheet is blank..

Dim twfn As String
Dim lastrow As Long

twfn = ThisWorkbook.Name

With Workbooks(twfn).Sheets("Compiled Sheets")

On Error Resume Next
      
lastrow = .Cells.Find(What:="*", searchorder:=xlByRows, _
 searchdirection:=xlPrevious).Row

If Err <> 0 Then lastrow = 0
    
End With

MsgBox lastrow

..works much better. Sorry!