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

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

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.
 

Forum statistics

Threads
1,171,429
Messages
5,875,491
Members
433,131
Latest member
ThatOneDude

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