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?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,611
Members
414,080
Latest member
penguin23

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