that's clever ! I like it

Thanks

"Bob Phillips" wrote:

Try

LastRowNumber = Range("D" & Rows.Count).End(xlUp).Row
MoveUp = LastRowNumber - 2
Cells(LastRowNumber + 1, "D").FormulaR1C1 = "=SUBTOTAL(9,R[-" & MoveUp &
"]C:R[-1]C)"


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Chris G" wrote in message
...
Hi,

I wish to put a total AT THE BOTTOM of a list of numbers in a macro.
The list is variable length and I want to use the subtotal(109, function
as
there will be filters applied to the data and I only wish it to add items
displayed.

I tried this but it crashed on the last line. Help Please!!
LastRowNumber = Range("D65536").End(xlUp).Row
Moveup = LastRowNumber - 2
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(1, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(109,R[-Moveup]C:R[-1]C)"