Microsoft Excel —

Suppress Subtotals for Multiple Row Fields

As soon as you have more than one row field, Excel automatically adds subtotals for all but the innermost row field. However, you may want to suppress subtotals for any number of reasons. Although this may be a relatively simple task to accomplish manually, the VBA code to suppress subtotals is surprisingly complex.

You must set the Subtotals property equal to an array of 12 False values. Read the VBA help for all the gory details, but it goes something like this: The first False turns off automatic subtotals, the second False turns off the Sum subtotal, the third False turns off the Count subtotal, and so on. It is interesting that you have to turn off all 12 possible subtotals, even though Excel displays only one subtotal. This line of code suppresses the Product subtotal:

PT.PivotFields("Line of Business").Subtotals = Array(False, False, False, False, _
    False, False, False, False, False, False, False, False)

A different technique is to turn on the first subtotal. This will automatically turn off the other 11 subtotals. You can then turn off the first subtotal to make sure that all subtotals are suppressed:

PT.PivotFields("Line of Business").Subtotals(1) = True
PT.PivotFields("Line of Business").Subtotals(1) = False