VBA - Remove all data values in pivot

ExcelNoob222

New Member
Joined
Jun 17, 2020
Messages
35
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!!
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

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,130,405
Messages
5,641,943
Members
417,248
Latest member
BStew

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