Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. : -- Lizz45ie ------------------------------------------------------------------------ Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410 View this thread: http://www.excelforum.com/showthread...hreadid=482977 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. : -- Lizz45ie ------------------------------------------------------------------------ Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410 View this thread: http://www.excelforum.com/showthread...hreadid=482977 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. : -- Lizz45ie ------------------------------------------------------------------------ Lizz45ie's Profile: http://www.excelforum.com/member.php...o&userid=23410 View this thread: http://www.excelforum.com/showthread...hreadid=482977 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotals problem: Excel 2003 (not, AFAIK, the nested subtotals bug) | Excel Discussion (Misc queries) | |||
Original subtotals should not be within nested subtotals in excel | Excel Worksheet Functions | |||
How do I copy an outline w/ subtotals & paste just the subtotals | Excel Discussion (Misc queries) | |||
Problem with nested subtotals, placing secondary subtotals BELOW . | Excel Discussion (Misc queries) | |||
why are nested subtotals coming out below outer subtotals? | Excel Worksheet Functions |