Last Row: Subscript out of Range error

[JackDanIce]

Hi,

Have function to find last row on a specified column and sheet but decided to use code from Ron De Bruin aka:

Public Function LastRow(ByRef wks As Worksheet, Optional ByRef xCol As Long = 1) As Long
    
    With wks
        '#1  LastRow = .Cells(.Rows.Count, xCol).End(xlUp).Row
        '#2  LastRow = .Cells.Find("*", wks.Cells(1, 1), xlPart, xlFormulas, xlByRows, xlPrevious).Row
    End With

    With wks
        '#3 
        LastRow = .Cells.Find(What:="*", After:=.Cells(1, 1), Lookat:=xlPart, LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
    End With


End Function

Commented out line #1 is what I initially used
Commented out line #2 errors with run-time error 9, subscript out of range
Line under #3 works but it's not clear why - the only difference I can see is not using the named argument parameters in #3 vs #2

Could someone explain why this is?

Thanks in advance,
JDI

 

[Fluff]

in #2 you have the xlPart & xlFormulas the wrong way round.
When using the parameter names (as in #3 ) you can put the parameters in whatever oder you like.
But when you omit the names you have to put parameters in the correct order. What,After,Lookin, Lookat, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat

 

[JackDanIce]

Understand, I must have copied it from the link wrong to mix up the parameters like that. Makes sense when name referencing, doesn't require specific order. Thanks @Fluff

 

[Fluff]

You're welcome