You can use Application.Caller:

Option Explicit
Function LastRowNum()

Dim myCell As Range

Application.Volatile

Set myCell = Application.Caller
LastRowNum = myCell.End(xlDown).Row

End Function

I added the application.volatile so that the UDF would re-evaluate whenever
excel recalculated. But I wouldn't trust the results of the UDF until I forced
a recalc (by hitting F9).

Another way around it is to pass the entire column for that formula:

Option Explicit
Function LastRowNum(myCol As Range)
Dim myCell As Range
Set myCell = Application.Caller
LastRowNum = myCell.End(xlDown).Row
End Function

And use it like:
=LastRowNum(I:I)
(when the formula is in a cell in column I)

By passing the column, excel realizes that when something changes in that
column, it has to reevaluate that UDF.



John wrote:

I have the following function to return the last row number in a column of
numbers:

Function LastRowNum()
Application.Volatile
LastRowNum = ActiveCell.End(xlDown).Row
End Function

Problem, I want it to return the last row number starting from the cell the
function is in, not starting from the active cell. ActiveCell obviously has
to come out, but what do I replace it with?

--

Dave Peterson