How to Clear Pivot Table Cache Using VBA in Excel

If we delete some data from the source table of a Pivot Table, in the Pivot Table that data will still be available because the deleted cells are still present in the cache of the Pivot Table. So, we need to clear the cache of the Pivot Table so it matches the modified dataset.

In this article, we will demonstrate how to clear the Pivot Table cache using VBA code in Excel.

Step-by-Step Procedure to Clear Cache from PivotTable Using VBA in Excel

Suppose we have the Sales Data of XYZ Company as our dataset. Let’s clear the Pivot Table cache using VBA code.

Step 1 – Insert a Pivot Table

First we’ll create a Pivot Table so that we can clear its cache.

Select the entire dataset.
Go to the Insert tab from Ribbon.
Select the PivotTable option from the Tables group.

The PivotTable from table or range dialog box will open.

Choose the Existing Worksheet option.
Select the destination cell in the Location field (here, cell F4).
Click OK.

The PivotTable Fields dialog box will open.

Drag the Employee Name field to the Rows section.
Drag the Sales field to the Values section.

We have the following Pivot Table in our worksheet.

Read More: How to Clear Pivot Cache in Excel

Step 2 – Delete Data from the Source Table

Now, we will delete data from our source table to create a cache mismatch.

Select the data to delete.
Go to the Home tab on the Ribbon.
Click on the Delete option from the Cells group.
Select the Delete Cells option from the drop-down.

A dialog box named Delete will pop up.

Choose the Shift cells up option.
Click OK.

The selected cells are deleted from the dataset.

Step 3 – Insert a Module to Write VBA Code

In order to write the VBA code, we need to create a blank Module.

Go to the Developer tab on the Ribbon.
Choose the Visual Basic option from the Code group.

The Microsoft Visual Basic for Applications window will open.

Go to the Insert tab.
Select the Module option from the drop-down.

Read More: How to Clear Excel Cache Using VBA

Step 4 – Write and Save the VBA Code

Enter the following code in the newly created Module1:

Private Sub clear_pivotTable_cache()
Dim table As PivotTable
Dim Worksheet As Worksheet
Dim cache As PivotCache
Application.ScreenUpdating = False
For Each Worksheet In ActiveWorkbook.Worksheets
For Each table In Worksheet.PivotTables
table.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next table
Next Worksheet
For Each cache In ActiveWorkbook.PivotCaches
On Error Resume Next
cache.Refresh
Next cache
Application.ScreenUpdating = True
End Sub

Code Breakdown

We initiated a sub-procedure named clear_pivotTable_cache.
We declared three variables named table as PivotTable, Worksheet as Worksheet, and cache as PivotCache.
We set the Application.ScreenUpdating property as False.
We used two nested For Next loops to remove the excess data from the Pivot Table.
We used another For Next loop to refresh the cache.
We set the Application.ScreenUpdating property as True.
We ended the sub-procedure.