Subtotal \ Grand Total in VBA

[mrdata]

I need to subtotal a range at each change in "CTR" with VBA code.
I acheived this with a macro recording but I can't figure out the next
part which is this.

I need to get the Grand total that appears at the bottom of the sum
range
To show up at the top of the worksheet in range ("P2").


I tried to make the cell equal xlSUM but that always gives -4175


Thanks
Charles

 

[Guest]

It sounds like you are using the built in subtotal method. Assume column P
contains the values you want to grandtotal.

After your code that builds the subtotal, try putting in lines of code like
this

set rng = Range("P3",cells(rows.count,"P").End(xlup))
Range("P2").formula = "=subtotal(9," & rng.Address & ")"

Adjust to fit your actual locations.

 

[mrdata]

Tom:
The code works great but it does not give me the result's I need.

First I only want to have a grand total in range P2
Currently I get the grand total below the data and in range P2

Also Is there a way to format the subtotal and grand total results to
show thousands. without having to do it manualy.

Here is the code I am using.


Range("A4").Select
Selection.Subtotal GroupBy:=11, Function:=xlSum, TotalList:=Array(9),
_
Replace:=True ', PageBreaks:=False ', SummaryBelowdata:=True

Set rng = Range("I5", Cells(Rows.Count, "I").End(xlUp))
Range("P2").Formula = "=subtotal(9," & rng.Address & ")"

I have the Summarybelowdata part remed out but it still gives a Grand
total
Below data.


Thanks
Charles