[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Pivot Table Labels in dropdowns

Posted on 2009-04-21
12
Medium Priority
?
406 Views
Last Modified: 2012-06-27
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
Comment
Question by:Charms55
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24200861
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
 

Author Comment

by:Charms55
ID: 24201227
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24203585
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:Charms55
ID: 24203852
That didn't seem to do it at all. What am I missing?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24203980
I think you will need to undo all your groupings and then redo them to remove the old groups.
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 2000 total points
ID: 24205079
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
 

Author Closing Comment

by:Charms55
ID: 31573317
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24206184
Nicely done, Jerry!
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 24206508
Thanks Patrick!
0
 

Expert Comment

by:CodeOrange
ID: 24272276
Thanks, Jerry! Nicely done.
0
 

Author Comment

by:Charms55
ID: 24644071
Please replace the file in comment #ID: 24203852 with the attached file
0
 

Author Comment

by:Charms55
ID: 24644317
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question