• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Pivot Table Labels in dropdowns

I have a list in Excel from which I have created several pivot tables and charts. When I was testing the list, I entered various data and when I sent the list out for managers to enter data, created a [blank] entry to include the formulas.

Now, even through the dummy data has been deleted and the blank replaced, in the pivot tables it still lists these items in filters. How do I remove those items once and or all?
sample.jpg
0
Charms55
Asked:
Charms55
  • 5
  • 3
  • 2
  • +2
1 Solution
 
Patrick MatthewsCommented:
Hello Charms55,

The code below may help clear out unneeded items.

Regards,

Patrick
Sub Delete_Unused_PivotFields() 
    ' This code originally is taken from Experts Exchange question:
    ' http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21318662.html
    ' Author: Dennis Wallentin (a.k.a. XL-Dennis)
    
    ' This sub will:
    ' 1) Loop through all worksheets in the ActiveWorkbook
    ' 2) Loop through each PivotTable on each worksheet
    ' 3) Loop through each PivotField in the table
    ' 4) Try to delete each PivotItem for each field
    
    ' Excel will not allow a deletion of a PivotItem that has at least one detail record
    ' in the source.  The On Error Resume Next error handler enables to sub to carry on despite
    ' the error that would occur
    
    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim ptTable As PivotTable
    Dim ptField As PivotField
    Dim ptItem As PivotItem
    Dim i As Long
    
    Set wbBook = ActiveWorkbook
    
    On Error Resume Next
    
    For i = 1 To 2
        For Each wsSheet In wbBook.Worksheets
         For Each ptTable In wsSheet.PivotTables
            For Each ptField In ptTable.PivotFields
               For Each ptItem In ptField.PivotItems
                  ptItem.Delete
               Next ptItem
            Next ptField
            ptTable.RefreshTable
         Next ptTable
       Next wsSheet
    Next i
    
    On Error GoTo 0 
End Sub

Open in new window

0
 
Charms55Author Commented:
I have very low VB skills, so what code do I need to alter and how do I run it when it is set and ready?
0
 
Patrick MatthewsCommented:
Charms55,

Go to the VB Editor, create a new module (NOT a class module), and paste the code there.  Then, go back
to Excel and hit Alt+F8 to bring up the run macro dialog.

Regards,

Patrick
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Charms55Author Commented:
That didn't seem to do it at all. What am I missing?
0
 
Rory ArchibaldCommented:
I think you will need to undo all your groupings and then redo them to remove the old groups.
0
 
Jerry PaladinoCommented:
Charms55,
Please try the VBA routine below.  I tested it on your sample file from yesterday and it cleared the blank values without ungrouping them.  Just follow Partrick's iinstruction above on placing this in a VBA module and then run it.  It should resolve all the pivot table in the workbook.
Thanks,
Jerry

Sub ClearUnusedPivotCacheItems()
Dim pt As PivotTable
Dim ws As Worksheet
 
For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
    pt.PivotCache.Refresh
  Next pt
Next ws
 
End Sub

Open in new window

SAMPLE-CFR-2009-1-.xls
0
 
Charms55Author Commented:
The solution is excellent and did the trick. My end users will not be confused by seeing all those extraneous data. Unfortunately, my grasp of VB doesn't allow me to fully understand what's going on under the hood!

Thanks so much!
0
 
Patrick MatthewsCommented:
Nicely done, Jerry!
0
 
Jerry PaladinoCommented:
Thanks Patrick!
0
 
CodeOrangeCommented:
Thanks, Jerry! Nicely done.
0
 
Charms55Author Commented:
Please replace the file in comment #ID: 24203852 with the attached file
0
 
Charms55Author Commented:
06.17.2009 at 10:07AM JST, ID: 24644071
Please replace the file in comment #ID: 24203852 with the attached file


SAMPLE-2009.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now