Sum a Column of Variable length

C

Chris G

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

Chris G

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.
 
B

Bob Phillips

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

Chris G

that's clever ! I like it

Thanks

Bob Phillips said:
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 said:
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)"
Click to expand...
Click to expand...
 
B

Bob Phillips

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 said:
that's clever ! I like it

Thanks

Bob Phillips said:
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 said:
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,
Click to expand...
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)"
Click to expand...
Click to expand...
Click to expand...
 
Top