How to Clear Pivot Table Cache Using VBA in Excel

Get FREE Advanced Excel Exercises with Solutions!

In Excel, we often need to use the PivotTable option to summarize our data. If we delete some data from the source table of the Pivot Table, some cells will be deleted from the source dataset, but in the Pivot Table, those data will still be available. Because data of those deleted cells are still available in the cache of the Pivot Table. So, we need to clear the cache of Pivot Table to match the PivotTable with the new dataset. In this article, we will learn five easy steps to clear the Pivot Table cache using VBA code in Excel. So, let’s start this article and explore these steps.


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

In this section of the article, we will discuss five simple steps to clear the cache from PivotTable using VBA code in Excel. Let’s say, we have the Sales Data of XYZ Company as our dataset. Our goal is to clear the cache from Pivot Table using the VBA code in Excel.

excel vba clear pivot cache

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.


Step 01: Insert Pivot Table

To clear the cache from Pivot Table, first, we need to create a PivotTable using our dataset. Let’s follow the instructions outlined below to insert a PivotTable.

  • Firstly, select the entire dataset.
  • After that, go to the Insert tab from Ribbon.
  • Then, select the PivotTable option from the Tables group.

Insert Pivot Table to clear the PivotTable cache using VBA code in Excel

As a result, the PivotTable from table or range dialogue box will open on your worksheet.

  • Now, in the PivotTable from table or range dialogue box, choose the Existing Worksheet option.
  • Then, select your destination cell in the Location field. In this case, we selected cell F4 as our destination cell.
  • Following that, click OK.

Consequently, the PivotTable Fields dialogue box will appear on your worksheet as shown in the following image.

  • Now, in the PivotTable Fields task pane, drag the Employee Name field to the Rows section.
  • Afterward, drag the Sales field to the Values section.

Editing PivotTable Fields dialogue box to clear the PivotTable cache using VBA code in Excel

Subsequently, you will have the following Pivot Table on your worksheet.

Final output of Step 01 to clear the PivotTable cache using VBA code in Excel

Read More: How to Clear Pivot Cache in Excel 


Step 02: Delete Data from Source Table

Now, we will delete data from our source table. To delete data, we will follow the steps mentioned below.

  • Firstly, select the data you want to delete.
  • Then, go to the Home tab from Ribbon.
  • After that, click on the Delete option from the Cells group.
  • Now, select the Delete Cells option from the drop-down.

Delete Data from Source Table to clear the PivotTable cache using VBA code in Excel

As a result, a dialogue box named Delete will pop up on your worksheet.

  • Now, in the Delete dialogue box, choose the Shift cells up option.
  • Following that, click OK.

Editing Delete dialogue box to clear the PivotTable cache using VBA code in Excel

Subsequently, your selected cells will be deleted from the dataset.

Final output of step 02 to clear the PivotTable cache using VBA code in Excel


Step 03: Insert Module to Write VBA Code

Before writing the VBA code, we need to create a blank Module. Let’s use the procedure discussed in the following section to insert a Module.

  • Firstly, go to the Developer tab from Ribbon.
  • Following that, choose the Visual Basic option from the Code group.

Insert Module to Write VBA Code to clear the PivotTable cache using VBA code in Excel

As a result, the Microsoft Visual Basic for Applications window will appear on your worksheet.

  • Now, in the Microsoft Visual Basic for Applications window, go to the Insert tab.
  • Then, select the Module option from the drop-down.

Final output of step 03 to clear the PivotTable cache using VBA code in Excel

Read More: How to Clear Excel Cache Using VBA 


Step 04: Write and Save VBA Code

As we have created a blank Module, we can now write VBA code in it.

  • Now, write 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

Writing the VBA code Write and Save VBA Code

Code Breakdown

  • Firstly, we initiated a sub-procedure named clear_pivotTable_cache.
  • Then, we declared three variables named table as PivotTable, Worksheet as Worksheet, and cache as PivotCache.
  • After that, we set the Application.ScreenUpdating property as False.
  • Following that, we used two nested For Next loops to remove the excess data from the Pivot Table.
  • Then, we used another For Next loop to refresh the cache.
  • Now, we set the set Application.ScreenUpdating property as True.
  • Finally, we ended the sub-procedure.
  • After writing the code, click on the Save option.

Read More: Clear Excel Memory Cache Using VBA 


Step 05: Run VBA Code

After saving the code, we will run the code to clear the cache from PivotTable in Excel. Now, let’s use the instructions outlined below.

  • Firstly, click on the Run option as marked in the following image.

Note: You can also use the keyboard shortcut F5 to run the code.

Running the VBA code to clear the PivotTable cache using VBA code in Excel

Consequently, you will see that the deleted data is removed from Pivot Table also. This indicates that the caches are cleared from PivotTable.

Final output of step 5 to clear the PivotTable cache using VBA code in Excel


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Practice Section to clear the PivotTable cache using VBA code in Excel


Download Practice Workbook


Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to clear the Pivot Table cache using VBA code in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below.

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zahid Hasan
Zahid Hasan

Zahid Hassan, BSc, Industrial and Production Engineering, Bangladesh University of Engineering and Technology, has worked with Exceldmy for 1.5 years. He has written 95+ articles for Exceldemy. He has worked as an Excel & VBA Content Developer. He also worked as a VBA Developer for the Template team. Currently, he is working as a Junior Software Developer for the Excel Add-in project. He is interested in Software Development, Python, VBA, VB.NET, and Data Science, expanding his expertise in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo