Toggle subtotals...

[billy-boy]

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.

 

[BrianB]

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]

Thanks. Worked out very well...

Bill.