Hide Pivot Table Subtotals

Sometimes the Excel macro recorder creates code that gets you off to a good start. Other times, it’s not so helpful.

This week, I was working on a pivot table macro, and wanted to turn off all the Row Field subtotals. Fortunately, there is a handy command for this on the Ribbon, on the Design tab, under PivotTable Tools. One click, and the subtotals disappear, or reappear.

Let’s get the code for that, which I’m sure will be equally clean and simple. Ha!

Record the Steps

Full of optimism, developed over my years of working with Excel, I turned on the macro recorder. Then, I recorded the steps while I used the Do Not Show Subtotals command. I turned off the recorder, and opened the Visual Basic Editor.

In the screen shot below, you can see the code from my macro recording. Yikes! It didn’t look anything like a simple click of the button had been recorded.

Instead of just one line of code, a line had been recorded for each field in the source data, to turn off the subtotals individually.

I needed flexible code, for a variety of pivot table layouts, so this wasn’t going to be much help.

What the Code Does

To see what the macro recorder created, here is one line of the code, for the Category field:

ActiveSheet.PivotTables("PivotTable1") _
  .PivotFields("Category").Subtotals = _
    Array(True, False, False, False, _
      False, False, False, False, _
      False, False, False, False)

Each of the items in the Array represents one of the Subtotal options that you see when you right-click on a pivot field, and choose Field Settings.

Automatic Subtotals

The first item in the Array is for the Automatic and None settings

• if you click Automatic, that item in the Array is True
• if you click None, the first item in the Array is False

The other items in the Array represent the Custom subtotal functions, in the order that they are listed in the Field Settings window.

• Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, Varp

Automatic is the Boss

In the manual Field Settings, the Automatic setting is the boss. In the screen shot below, I selected several Custom subtotal functions.

But, as soon as I click Automatic, those Custom subtotals are ignored – only the Automatic subtotal will be shown when I click OK.

The same thing happens in the VBA code:

• If the first item is True, it doesn’t matter if any of the other items are set to True – they will be ignored.

Here is another version of the code, for the Category field, with the first 3 items changed to True:

ActiveSheet.PivotTables("PivotTable1") _
  .PivotFields("Category").Subtotals = _
    Array(True, True, True, True, _
      False, False, False, False, _
      False, False, False, False)

If I run that code, only the Automatic subtotals will appear.

However, if I change the first item to False, the Custom subtotals for Sum, Count and Average are shown, when I run the code, because Automatic subtotals are turned off.

ActiveSheet.PivotTables("PivotTable1") _
  .PivotFields("Category").Subtotals = _
    Array(False, True, True, True, _
      False, False, False, False, _
      False, False, False, False)

Here is the pivot table with the custom subtotals.

Just the Automatic Setting

Since I’m only interested in turning subtotals off, I can focus on the Automatic setting, and ignore all the others.

If you look in the VBA help for the PivotField.Subtotals property, you’ll see that you can use an index number, instead of an Array.

The index number for Automatic is 1, so I can turn subtotals on with this line of code:

ActiveSheet.PivotTables("PivotTable1") _
  .PivotFields("Category").Subtotals(1) = True

Or, turn Automatic subtotals off by setting index 1 to False:

ActiveSheet.PivotTables("PivotTable1") _
  .PivotFields("Category").Subtotals(1) = False

Create Flexible Code

To make the code more flexible, I didn’t want to name specific pivot fields – I wanted the code to turn off subtotals on all the fields. So, I created variables for pivot table and pivot field.

The code loops through all the pivot tables on the active sheet. In each pivot table, the code loops through all the pivot fields, and sets the Subtotals(1) to Automatic (to turn off any other Subtotals), then sets Subtotals(1) to False, to turn subtotals off.

NOTE: To save time in a large pivot table, you could change “.PivotFields” to “.RowFields” or “.ColumnFields”

Sub NoSubtotals()
'pivot table tutorial by contextures.com
'turns off subtotals in pivot table

Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
  pt.ManualUpdate = True
  For Each pf In pt.PivotFields
    'First, set index 1 (Automatic) to True,
    'so all other values are set to False
    pf.Subtotals(1) = True
    pf.Subtotals(1) = False
  Next pf
  pt.ManualUpdate = False
Next pt

End Sub

Download the Sample File

To test the NoSubtotals code, you can download the sample file from the Show and Hide Pivot Table Items page on my Contextures website. Use the data from the FoodSales sheet.

Paste the NoSubtotals sample code onto a regular module, and save the workbook as macro enabled.

_______________