lastRow Function code

BratFire

Board Regular
Joined
Aug 2, 2005
Messages
99
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:
Code:
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?
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

hrithik

Active Member
Joined
Jul 26, 2010
Messages
336
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

Board Regular
Joined
Aug 2, 2005
Messages
99
I copied your code and received the same result.

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

BratFire

Board Regular
Joined
Aug 2, 2005
Messages
99
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,651
Messages
5,854,954
Members
431,689
Latest member
jacker01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top