Toggle subtotals...
 

I'm trying to use the same macro to 'toggle' the sub-total function in
Excel. I can currently apply a subtotal to the selected data range,
but I also want to remove all subtotals (if any exist) by using the
same macro.

Is there any poperty that will determine whether subtotals exist on a
worksheet?

Thanks,
Bill.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Toggle subtotals...
 

Try this. Uses fact that ordinary data rows' OutlineLevels become
greater than 1 when SubTotals applied.

'-------------------------------------------
Sub ApplySubtotals()
' Check a row known to be ordinary data
If ActiveSheet.Rows(2).OutlineLevel = 1 Then
ActiveSheet.Range("A1").Subtotal _
GroupBy:=1, Function:=xlSum, TotalList:=Array(2, 3, 4, 5), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Else
ActiveSheet.Range("A1").RemoveSubtotal
End If
End Sub
'----------------------------------------------

Regards
BrianB
===========================================


billy-boy wrote in message ...
I'm trying to use the same macro to 'toggle' the sub-total function in
Excel. I can currently apply a subtotal to the selected data range,
but I also want to remove all subtotals (if any exist) by using the
same macro.

Is there any poperty that will determine whether subtotals exist on a
worksheet?

Thanks,
Bill.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Toggle subtotals...
 

Thanks. Worked out very well...

Bill.



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/