Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A1:B(xlDown)? | Excel Programming | |||
End(XlDown).Row - Problem if only 1 row | Excel Programming | |||
End(xlDown) not working? | Excel Discussion (Misc queries) | |||
repeated end(xldown) | Excel Programming | |||
XlDown: Go to next blank row | Excel Programming |