Welcome Excellers to another #Excel #MacroMonday blog post in my 2020 series. Let’s look at creating another Excel VBA Macro to delete obsolete items from your Excel Pivot Table. Let’s speed up our Excel life. If like me you reuse Pivot Tables over and over again. Sometimes, your data source changes or products no longer being sold or one of your salespeople no longer working for you. You may find this happen especially if you use your Excel Pivot Table as a template that when your data is refreshed that old items remain in the Table Field dropdowns.
Manually Delete Obsolete Pivot Table Items.
From Excel 2007 onwards it is really quite simple to remove these old items, we can use the Change Retain Items function.
- Right click a cell in your data table
- Pivot Table options
- Select Data Tab
- In Retain Items Section select None in the drop down box
- Hit ok
- Refresh your Pivot Table
This is a really easy way to keep your data all fresh a relevant. In earlier i.e from Excel 2002 onwards versions of Excel, there are two ways to clear any old items. If you want to watch my YouTube Video just click the screen below.
First, if you manually created any groups that contain your old items you need to
- Ungroup these items
- Drag the pivot field out of the pivot table
- Hit the refresh button on the pivot table toolbar
Delete Obsolete Pivot Table Items With An Excel Macro.
If you want to use some simple Visual Basic (VB) code then you can use this following routine to programmatically remove old items from your Pivot Tables. Who wouldn’t want to automatically carry out the same process?. Yay for macros!.
Starting The VBA Macro.
First, you will need to open the Visual Basic Editor. There are two ways to do this.
- Either by hitting ALT +F11 or
- Selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result.
If you cannot see the Developer Tab, then you may need to enable it. Feel free to watch my YouTube video on enabling the Developer Tab.
Before you begin to write any code you need to decide where to store the code. You have a choice.
- To store your code either in your Personal Macro Workbook or
- Store it in your current workbook
What Is The Difference In The Locations?.
So, there is a difference. If you save the code in your Personal Macro workbook it will be available in any Excel workbooks. If you store it in the current workbook then use is restricted to that workbook. In this instance, want this code to run when the workbook opens. So it will be saved in the current workbook.
If however, you want more details on creating and updating your personal macro workbook then I recommend my blog posts below.
Creating and Updating Your Personal Macro Workbook
Why Is My Personal Macro Workbook Not Loading Automatically?
Create A Shortcut To Your Personal Excel Macro Workbook
Writing The Macro.
To being this macro I need to insert a new module in the current workbook. This is easy. Simply double click on ‘This Workbook’ in the Project Explorer Window.
Above the code in the two drop-down boxes select Workbook on the left, then go ahead and select Open on the right. This is where we write our code. You can see Excel has already put the first and last lines of code in there for us.
Declaring Variables.
We need to declare a few variables in the code. This simple means that Excel creates a type of memory container for these values.
[stextbox id=’info’]
Dim pt As PivotTable, ws As Worksheet, pc As PivotCache
[/stextbox]
Using The For Each Loop.
Now, the For Each Looping method is used to loop through every Pivot Table in the active worksheet. we use the PivotCache.MissingItemsLimit property. This sets the maximum quantity of unique items per PivotTable field that are retained even when they have no supporting data in the cache records.
[stextbox id=’info’]
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
Next pt
Next ws
[/stextbox]
Refresh The Pivot Tables. For Each Loop.
The next part of the code refreshes the Pivot Tables and that will clear any old items that are no longer relevant to your data set once your workbook is opened. Again the For Each loop is used to loop through each of the Pivot Tables in the active workbook.
[stextbox id=’info’]
For Each pc In ActiveWorkbook.PivotCaches
On Error Resume Next
pc.Refresh
Next pc
[/stextbox]
Ending The Macro.
Once the Pivot Tables have been refreshed, the code ends. This instruction was already entered into the module for us when started the type the name of the Macro. The Macro will run when the workbook is opened.
[stextbox id=’info’]
End Sub
[/stextbox]