Skip to main content

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.

Most VBA code should be placed in Standard Modules unless specified.

If you see a comment '------------------ Modules------------------ in the code header that means put the code in a Standard Module. For more information, learn this course: Where should I put the Excel VBA code?

The following steps teach you how to put VBA code 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 in the newly created module. You will probably need to change the sheet name, the range address, and the save location.
  5. Click Run button on the Visual Basic Editor toolbar.
  6. For more information, learn this course: Programming with Excel VBA

Leave a comment

Your email address will not be published. Required fields are marked *

Format your code: <pre><code class="language-vba">place your code here</code></pre>