Excel Macro: Hide All Subtotals in a PivotTable

This simple macro hides all subtotals in the specified PivotTable.

Hide All Subtotals in a PivotTable

Sub HideSubtotals()
'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

How This Macro Works

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 can be run only when the cursor is inside a specific PivotTable.

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. Step 4 uses For Each...Next statement to loops through all the fields in the PivotTable. 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 This Macro