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

When I say "in a macro" I really mean "using a macro".
The list of numbers is in the spreadsheet and the formula is to be put in
the spreadsheet. Sorry about any potential confusion.

"Chris G" wrote:

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

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

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

I had to do it that way as in my test data the last cell was in a completely
different column <G

--

HTH

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


"Chris G" wrote in message
...
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)"