Zahid Hasan
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.
Download Practice Workbook
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.
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.
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.
Subsequently, you will have the following Pivot Table on your worksheet.
Read More: How to Clear Pivot Cache in Excel (3 Effective Ways)
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.
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.
Subsequently, your selected cells will be deleted from the dataset.
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.
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.
Read More: How to Clear Excel Cache Using VBA (4 Easy Methods)
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
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 (3 Easy Ways)
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.
Consequently, you will see that the deleted data is removed from Pivot Table also. This indicates that the caches are cleared from PivotTable.
Practice Section
In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.
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. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.
