Using xlup in macro sum formula

Posted by Bob M on June 21, 2001 4:11 PM

I have a sheet that can have a variable number of rows. I see that I can use xlup/down to select a range, but how can I use this in a sum formula within a macro? Recording the macro doesn't cut it -- the normal reference records the exact cells and a relative reference just records the row offsets. I need a formula similar to: sum=(r-1,c0 : xlup). Anyone know if this is possible and what the syntax is??

Posted by Ivan F Moala on June 22, 2001 12:40 AM

something along the lines of this may help

Sub test()
Dim Ttl As Variant
Dim TtlRg As Range

Set TtlRg = Range(ActiveCell, ActiveCell.End(xlUp))
Ttl = Application.WorksheetFunction.Sum(TtlRg)

End Sub

Note: just change xlup to xldown, xltoleft,xltoright as required

HTH
Ivan