How to remove all calculated items in a PivotTable using VBA?

Removing all calculated items from a PivotTable using VBA involves iterating through the PivotTable fields and deleting each calculated item. Below is a VBA code example that demonstrates how to accomplish this:

Sub RemoveAllCalculatedItems()
    Dim ws As Worksheet
    Dim pt As PivotTable
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim i As Long
    
    ' Set the worksheet and pivot table
    Set ws = ThisWorkbook.Worksheets("Sheet1") ' Change to your sheet name
    Set pt = ws.PivotTables("PivotTable1")     ' Change to your pivot table name
    
    ' Loop through each pivot field in the pivot table
    For Each pf In pt.PivotFields
        ' Check if there are calculated items in the field
        If pf.CalculatedItems.Count > 0 Then
            ' Loop through each calculated item and delete it
            For i = pf.CalculatedItems.Count To 1 Step -1
                pf.CalculatedItems(i).Delete
            Next i
        End If
    Next pf
    
    ' Refresh the pivot table to apply the changes
    pt.RefreshTable
    
    MsgBox "All calculated items have been removed."
End Sub

Explanation:

Set the Worksheet and PivotTable: Assign your worksheet and pivot table names to the `ws` and `pt` variables, respectively.
Iterate Through PivotFields: Use a `For Each` loop to iterate through each PivotField in the PivotTable.
Check for Calculated Items: Use the `CalculatedItems.Count` property to check if there are any calculated items in the current field.
Delete Calculated Items: If calculated items exist, loop through them in reverse order (from last to first) and delete each one using the `Delete` method.
Refresh the PivotTable: After removing all calculated items, refresh the PivotTable to update the display.
Notification: Optionally, show a message box to confirm that all calculated items have been removed.

Before running this macro, make sure to replace `”Sheet1″` and `”PivotTable1″` with the actual names of your worksheet and PivotTable. This script assumes you are working with the relevant worksheet and PivotTable configurations.

facebooklinkedin