Clear Excel Memory Cache Using VBA (3 Easy Ways)

This article describes three straightforward Excel VBA methods for clearing cache memory, a process which improves application speed.

We’ll use the following sample dataset containing Product, Sales Rep, and State columns to demonstrate our methods.

We used Microsoft Excel 365 in this article. If you are using a different version and any steps don’t work, please let us know in the comments below.

Method 1 – Combine PivotTable and PivotCache Objects to Clear Memory Cache

A Pivot Cache is generated automatically whenever a Pivot Table is created. Clearing the Pivot Table cache erases that part of the Excel memory cache.

STEPS:

• Select the B4:D10 range.
• Navigate to the Insert tab and click on PivotTable.

The PivotTable window will appear.

• Enter the sheet name with an Exclamation mark, followed by the range in the Table box.
• Check the New Worksheet option.
• Click OK.

The PivotTable Fields pane will open on the right side.

• Check all the dataset columns.

The Pivot Table like the one below will be generated.

• Go to the Developer tab, followed by Visual Basic.

• Click Insert, followed by Module.

• Enter the following code in the Module Box:

Sub CleanPivotCache()
Dim softEkoTable As PivotTable
Dim softEkoSheet As Worksheet
Dim softEkoCache As PivotCache
Application.ScreenUpdating = False
For Each softEkoSheet In ActiveWorkbook.Worksheets
For Each softEkoTable In softEkoSheet.PivotTables
softEkoTable.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next softEkoTable
Next softEkoSheet
For Each softEkoCache In ActiveWorkbook.PivotCaches
On Error Resume Next
softEkoCache.Refresh
Next softEkoCache
Application.ScreenUpdating = True
End Sub

• Press  F5  or click the Run button.

Excel’s memory cache will be cleared.

Method 2 – Clean Memory Cache by Assigning Nothing Literal to Objects

The literal descriptor, Nothing, can only be used with variables specified as entities or variants. If the reference value of an object property is zero, then that variable is effectively uninitialized. In this context, we will create an object called softEkoObject and set it to Nothing literal, which will clear Excel’s memory cache for the specified objects.

STEPS:

• Open a sheet and make it the active sheet.
• Click on the Developer tab, then Visual Basic.

• Click Insert then the Module icon.

• Copy and paste the code below into the Module Box. Remember to modify the sheet name and the range for your worksheet.

Sub nothingLiteralToAObject()
     Dim softEkoObject As Object
 Set softEkoObject = Application.Worksheets("NothingLiteral").Range("B4:D10")
         Set softEkoObject = Nothing
End Sub

• Press F5 or click the Run button.

The macro clears the memory cache.

Method 3 – Assign Zero to RecentFiles Properties to Clear Excel Memory Cache

Excel’s RecentFiles property lists the most recently used files. We can use a VBA macro to set the RecentFiles property to 0, which will clear the memory cache of Recent Files data.

• Navigate to the Developer tab and click the Visual Basic symbol.

• Choose Insert, followed by the Module icon.

• Enter the code below in the Module Box:

Sub clearMemoryCache()
    Application.RecentFiles.Maximum = 0
End Sub

• Press F5 or click the Run button.

The memory cache is cleared.

Download Practice Workbook

<< Go Back to Clear Cache in Excel | Excel Files | Learn Excel