Using Subtotals in VBA
 

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. : :confused:


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=482977

Using Subtotals in VBA
 

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)


"Lizz45ie" wrote in
message ...

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. : :confused:


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile:
http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=482977

Using Subtotals in VBA
 

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" wrote:


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. : :confused:


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=482977

Using Subtotals in VBA
 

Thanks Bob that worked. :)


--
Lizz45ie
------------------------------------------------------------------------
Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410
View this thread: http://www.excelforum.com/showthread...hreadid=482977