VBA - Remove all data values in pivot

[ExcelNoob222]

Hi,

I have the below code that removes all data values and custom calc values from the pivot table. However, I find it is a bit slow to run so I am wondering if there is more efficient code to do the same thing.

Sub RemovePivData()
'   Removed fields from the data area of pivot table

    Dim PT As PivotTable, ptField As PivotField, ptItem As PivotItem
    Set PT = ActiveSheet.PivotTables("PivotTable1")
    For Each ptField In PT.DataFields
        On Error GoTo CalcField
        ptField.Orientation = xlHidden
        On Error GoTo 0
    Next ptField
    Set PT = Nothing
    Exit Sub

CalcField:
    Set ptItem = ptField.DataRange(1).PivotItem
    ptItem.Visible = False
    Set ptItem = Nothing
    Resume Next
End Sub

Alternatively, I tried the below code, which is much faster and okay as I can add my headers again easily. But the issue is this deletes my custom calculations instead of just removing them.

ActiveSheet.PivotTables(1).ClearTable()

Thanks!!

 

[tr1face]

Hi,

You can try adding
Application.ScreenUpdating = False ; Application.Calculation = xlCalculationManual after Sub () line ; while for these changes to revert after your code runs you can add Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic before the End Sub line