Using Subtotals in VBA

[Lizz45ie]

Hello,
I'm trying to sub-totals in macro where the last row will vary.
Currently I'm using the code that the Macro recorder recorded but it
doesn't work if there are more rows than what's in the macro. The
macro recorded:

ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[1068]C)"

I've tried ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-" & C3 &
"]C:R[-1]C)" but it doesn't return totals. My subtotals are at the top
of my columns - I don't if that makes a difference.

Please help. :

 

[Bob Phillips]

Maybe

Dim iLastrow As Long

iLastrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[2]C:R[" & iLastrow & "]C)"


--

HTH

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

 

[exceluserforeman]

Adds a list of numbers

Sub addsup()
Dim T As Object
Dim B As Object

Range("E3").Select
Set T = Selection
Selection.End(xlDown).Select
Set B = Selection
ActiveCell.Offset(1, 0).Select
Selection.Formula = "=sum(" & T.Address & ":" & B.Address & ")"

End Sub

 

[Lizz45ie]

Thanks Bob that worked.