Skip to content

Excel VBA Macro to Show/Hide Subtotals in a PivotTable

    To control subtotal visibility and calculation use the Subtotals property of the PivotField object. The index number controls the calculation performed as shown in the table below.

    IndexMeaning
    1Automatic
    2Sum
    3Count
    4Average
    5Max
    6Min
    7Product
    8Count Nums
    9StdDev
    10StdDevp
    11Var
    12Varp
    Dim ws As Worksheet
    Set ws = ActiveSheet
    Dim wb As Workbook
    Set wb = ActiveWorkbook
    Dim PTcache As PivotCache
    Dim PT As PivotTable
    'Define the cache for the PivotTable
    Set PTcache =wb.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:=Range("Sales_Data[#All]"),Version:=xlPivotTableVersion15)
    'Create the PivotTable
    Set PT =ws.PivotTables.Add(PivotCache:=PTcache, _
    TableDestination:=Range("J1"),TableName:="Sales Analysis")
    PT.AddFieldsRowFields:=Array("Region", "Branch"), _
    ColumnFields:="Product",PageFields:="Product Category"
    PT.AddDataFieldField:=PT.PivotFields("Revenue"), _
    Caption:="No ofTransactions", Function:=xlCount
    'Remove subtotals from Region
    PT.PivotFields("Region").Subtotals(1) = False