VBA - Remove all data values in pivot

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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.

VBA Code:
ActiveSheet.PivotTables(1).ClearTable()

Thanks!!
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

tr1face

New Member
Joined
Jan 7, 2021
Messages
10
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,321
Members
415,966
Latest member
ctorohuamanchumo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top