Macro 66: Hide All Subtotals in a PivotTable
When you create a PivotTable, Excel includes subtotals by default. This inevitably leads to a
PivotTable report that inundates the eyes with all kinds of numbers, making it difficult to analyze.
Figure 6-2 demonstrates this.
Figure 6-2: Subtotals can sometimes hinder analysis.
Manually removing Subtotals is easy enough; right-click the field headers and uncheck the Subtotal
option. But if you’re constantly hiding subtotals, you can save a little time by automating the process
with a simple macro.
you are building an automated process that routinely manipulates pivot tables without
manual intervention, you may prefer the macro option.
How it works
If you record a macro while hiding a Subtotal in a PivotTable, Excel produces code similar to this:
ActiveSheet.PivotTables(“Pvt1 ).PivotFields(“Region”).Subtotals =
Array(False, False, False, False, False, False, False, False, False,
False, False, False)
That’s right; Excel passes an array with exactly 12 False settings. There are 12 instances of False
because there are twelve types of Subtotals — Sum, Avg, Count, Min, and Max, just to name a few.
So when you turn off Subtotals while recording a macro, Excel sets each of the possible Subtotal
types to False.
An alternative way of turning off Subtotals is to first set one of the 12 Subtotals to True. This auto-
matically forces the other 11 Subtotal types to False. We then set the same Subtotal to False,
effectively hiding all Subtotals. In this piece of code, we are setting the first Subtotal to True, and
then setting it to False. This removes the subtotal for Region.
With ActiveSheet.PivotTables(“Pvt1 ).PivotFields(“Region”)
.Subtotals(1) = True
.Subtotals(1) = False
End With
In our macro, we use this trick to turn off subtotals for every pivot field in the active PivotTable.
Sub Macro66()
‘Step 1: Declare your Variables
Dim pt As PivotTable
Dim pf As PivotField
‘Step 2: Point to the PivotTable in the active cell
On Error Resume Next
Set pt = ActiveSheet.PivotTables(ActiveCell.PivotTable.Name)
‘Step 3: Exit if active cell is not in a PivotTable
If pt Is Nothing Then
MsgBox “You must place your cursor inside of a
PivotTable.” Exit Sub
End If
‘Step 4: Loop through all pivot fields and remove totals
For Each pf In pt.PivotFields
pf.Subtotals(1) = True
pf.Subtotals(1) = False
Next pf
End Sub
1. Step 1 declares two object variables. This macro uses pt as the memory container for the
PivotTable and uses pf as a memory container for the pivot fields. This allows us to loop
through all the pivot fields in the PivotTable.
2. This macro is designed so that we infer the active PivotTable based on the active cell. That is
to say, the active cell must be inside a PivotTable for this macro to run. The assumption is
that when the cursor is inside a particular PivotTable, we want to perform the macro action
on that pivot.
Step 2 sets the pt variable to the name of the PivotTable on which the active cell is found.
We do this by using the ActiveCell.PivotTable.Name property to get the name of
the target pivot.
If the active cell is not inside of a PivotTable, an error is thrown. This is why the macro uses
the On Error Resume Next statement. This tells Excel to continue with the macro if
there is an error.
3. Step 3 checks to whether the pt variable is filled with a PivotTable object. If the pt variable
is set to Nothing, the active cell was not on a PivotTable, thus no PivotTable could be
assigned to the variable. If this is the case, we tell the user in a message box, and then we exit
the procedure.
4. If the macro reaches Step 4, it has successfully pointed to a PivotTable. We are ready to loop
to all the fields in the PivotTable. We use a For Each statement to iterate through each
pivot field. Each time a new pivot field is selected, we apply our Subtotal logic. After all the
fields have been evaluated, the macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
