End(xlDown) function
 

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?

End(xlDown) function
 

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

End(xlDown) function
 

Function LastRowNum()
Application.Volatile
Set r = Range(Application.Caller.Address)
LastRowNum = r.End(xlDown).Row
End Function

--
Gary''s Student - gsnu200852


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

End(xlDown) function
 

This could cause trouble if the function is used in different sheets. The
unqualifed Range() will refer to the activesheet--not the sheet that contains
the formula.



Gary''s Student wrote:

Function LastRowNum()
Application.Volatile
Set r = Range(Application.Caller.Address)
LastRowNum = r.End(xlDown).Row
End Function

--
Gary''s Student - gsnu200852

"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