Sub total for dynamic range

[praneetbvb]

Hi I need to edit the formula for subtotaling in a dynamic range.



Sheet 1 : Standard: I need to subtotal in column B.

Here is the VBA code I am using;


Range("B1").Select
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[10000]C)"

Can some help me edit the code, so that the formula works for any number of rows?



Sheet 1 : Standard: I need to total in column I

I need to total 2 different columns at the bottom of the pivot table. But the number of rows are not going to be same, so can some body please help me edit the code, so that if totals 2 columns as per the formula at the bottom of the pivot

Here is the code:

ActiveWindow.SmallScroll Down:=21
Range("F40").Select
ActiveCell.FormulaR1C1 = "Total"
ActiveCell.FormulaR1C1 = _
"=GETPIVOTDATA(""Calc Cost"",R3C1,""Cost"",""05-LO"")+GETPIVOTDATA(""Calc Cost"",R3C1,""Cost"",""04-MO"")"