How to clear old PivotTable cache using VBA?

When working with PivotTables in Excel, each time you create a PivotTable, Excel saves a cache of data that can become outdated or unnecessary if the source data has changed. Clearing this cache can help ensure that your PivotTables are up-to-date and can also reduce the file size. You can use VBA (Visual Basic for Applications) to clear the old PivotTable cache. Here’s how you can do it:

Sub ClearOldPivotTableCache()
    Dim wb As Workbook
    Dim pc As PivotCache
    Dim i As Long
    
    ' Set reference to the current workbook
    Set wb = ThisWorkbook
    
    ' Loop through all the PivotCaches in the workbook
    For i = wb.PivotCaches.Count To 1 Step -1
        Set pc = wb.PivotCaches(i)
        ' Check if the PivotCache has a PivotTable
        If pc.PivotTables.Count = 0 Then
            ' Delete unused PivotCache
            pc.MissingItemsLimit = xlMissingItemsNone
            wb.PivotCaches(i).Delete
        End If
    Next i
    
    MsgBox "Cleared old, unused PivotTable caches.", vbInformation
End Sub

Open the Visual Basic for Applications Editor:

Press `Alt + F11` to open the VBA editor in Excel.

Insert a Module:

In the VBA editor, go to `Insert > Module` to create a new module. This is where you’ll write your VBA code.

Write the VBA Code:

You can use the following code to loop through all the PivotCaches in the workbook and clear those that are not being used:

Run the Macro:

To run the macro, press `F5` while in the VBA editor or close the editor and run the macro from the Excel interface by going to `Developer > Macros`, selecting `ClearOldPivotTableCache`, and clicking `Run`.

Important Notes:

This code deletes only the caches that are no longer used by any PivotTable. It will not affect the caches being used by current PivotTables.
Always ensure that you have a backup of your file before running macros that modify data, as changes made by VBA cannot be undone using the `Undo` feature in Excel.
If your workbook is large or contains many PivotTables, the operation might take some time.

By following these steps, you can effectively manage and clean up old PivotTable caches in Excel using VBA.

facebooklinkedin