Have you ever recorded a macro while hiding a Subtotal in a PivotTable? You basically get something like this:
ActiveSheet.PivotTables(“PivotTable1″).PivotFields(“Budget Status”).Subtotals = Array(False, False, False, False, False, False, False, False, False, False, False, False)
That’s right. There are twelve Falses here. What’s crazier than that, if you try to leave out any of Falses, the code bugs out.
So why all the annoying Falses?
Like most macro recording, Excel errs on the safe side, giving you too much information. The reason there are twelve instances of False is because there are twelve types of Subtotals.
If go the field settings on any of your fields, you can actually implement a Custom subtotal and specify the one or more from the list. Like so:
This means you can actually have varying types of Subtotals.
Note: One of the 12 subtotals is the internal “Automatic Subtotal”. You won’t see that one in the Custom Subtotal list.
So when you turn off Subtotals while recording a macro, Excel sets each of the possible Subtotal types to False. That’s all fine and dandy, but if you’re like me, this insane array of Falses, drives you completely bananas. Here’s an alternative way to code the hiding of subtotal:
With ActiveSheet.PivotTables(“PivotTable1″).PivotFields(“Budget Status”)
.Subtotals(1) = True
.Subtotals(1) = False
End With
We first set one of the Subtotals to True. This automatically forces the other 11 Subtotal types to False. We then set the same Subtotal to False, effectively hiding all Subtotals.
Now, I’m not saying my alternative is better. But it definitely looks a lot cleaner to me.
Another alternative is to write a function and create 12 Optional parameters, all False by default. i.e.
Function ShowSubtotals(ptName As String, fieldName As String, Optional st1 As Boolean = False, Optional st2 As Boolean = False, Optional st3 As Boolean = False, Optional st4 As Boolean = False, Optional st5 As Boolean = False, Optional st6 As Boolean = False, Optional st7 As Boolean = False, Optional st8 As Boolean = False, Optional st9 As Boolean = False, Optional st10 As Boolean = False, Optional st11 As Boolean = False, Optional st12 As Boolean = False)
Then you would just call it like this:
ShowSubtotals(“PivotTable1″,”Budget Status”)
to set them all to False.
Brilliant solution. Just what I needed.
Have been struggeling with RemoveSubtotals for hours.