Suppressing 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. Because I am unhappy with the pivot table's lack of capability to add a page break at each change in the product field, I have resigned myself that the subtotals are better added if I use the Subtotals method after converting the pivot table to a table.

Thus, while creating the pivot table in code, you will want to turn off the subtotal rows for the product field. To do this in the Excel user interface, double-click the Product button, and change the Subtotals option to None, as shown in Figure 12.15.

Figure 12.15

When you have more than one row field, eliminate the product subtotals. Double-click the Product button,and choose the None option under Subtotals.

Figure 12.15

When you have more than one row field, eliminate the product subtotals. Double-click the Product button,and choose the None option under Subtotals.

The VBA code to suppress subtotals is incredibly 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("Product").Subtotals = False, False, False, False, False,

Array(False, False, False, False, False, False)

Keep in mind there is absolutely no readability here. You cannot name the individual members of the array, so no other coder will be able to know that you are turning on the Count subtotal instead of the Min subtotal. If you have a report with three row fields, you might find it easier to define an array of 12 Falses once and assign this array to both row fields:

NoSubtotalArray = Array(False, False, False, False, False, False, False, False, _

False, False, False, False) PT.PivotFields("Product").Subtotals = NoSubtotalArray PT.PivotFields("Date").Subtotals = NoSubtotalArray

0 -1