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:
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?
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?