Hide subtotals of rowfields in a pivot table

H

Huyeote

Hi, all, I use VBA to generate a pivot table and Excel adds subtotal to all
row field headers. How can I hide all subtotals in VBA? I tried using
Excel's macro recording function, but it only delete single cell which
contain the subtotal header. Thanks

Huyeote
 
B

BrianB

Right click grey column header to get menu.
Field - SubTotals ... none
Options - uncheck Grand totals for columns/Grand totals for rows

With pivot tables it is always best to record macros because the
requirements are not exactly intuitive.
 
D

Debra Dalgleish

The following code will turn off the subtotals for all row and column
fields:

'=========================
Sub NoSubtotals()
'turns off subtotals in pivot table
'.PivotFields could be changed to
'.RowFields or .ColumnFields
Dim pt As PivotTable
Dim pf As PivotField
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
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
Next pt
End Sub
'========================
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top