lastRow Function code

[BratFire]

I did look at Colin's reply in this thread do not know if I should persue that method further...

Essentially what I am after is a formula that will determine the last row in a worksheet.

I use the following code to find the last row via VBA:

Function lastRow(Optional wsName As String) As Long
'    extention of my own function; base code provided by MWE vbaexpress.com
'    find the last populated row in a worksheet
     
    If wsName = vbNullString Then wsName = ActiveSheet.Name
    With Worksheets(wsName)
        On Error Resume Next
        lastRow = .Cells.Find("*", .Cells(1), xlFormulas, xlWhole, xlByRows, xlPrevious).Row
        If Err <> 0 Then lastRow = 0
    End With
     
End Function

When called via VBA code this function performs as expected. When used as a formula in cell "B2", for example, it always returns a value of zero. Would it be possible to use this function via VBA and as a worksheet formula with some modification?

 

[hrithik]

Try this:

Function LastRow(Sh As Worksheet)
On Error Resume Next
LastRow = Sh.Cells.Find(What:="*", _
After:=Sh.Range("A1"), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

 

[BratFire]

I copied your code and received the same result.

Other info: Using Excel 2003 and the code is in a module.

 

[BratFire]

Sorry to bother, would anyone else have the chance to test hrithik's code to see if it works as a worksheet function? It works when referenced in VBA, I could not get it to work as a worksheet function.

Thank you.