Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Debra Dalgleish's clearing old items from Pivot Table dropdowns
Hi.
I was hoping to direct this question directly to Debra as she wrote the code but if anyone else can assist feel free to reply. I used the code on http://www.contextures.com/xlPivot04.html (I'm using Excel 2000) to clear old items from the drop-downs. It took over 30 minutes to complete. On completion I clicked on a drop-down and Excel shut down - frustrating!! I repeated this procedure, saved the file on completion, closed the file and then tried to re-open the file. Again the file caused Excel to shut-down, this time before the file opened. Have you ever experienced this? I also tried the manual way suggested. Although it didn't crash it didn't get rid of the old items. I should also point out there are approximately 10 worksheets within the workbook, with the majority haveing a pivot table on them. I took your (and others) advice and used the same data to use less data. Does this create a problem? Any suggestions would be greatly appreciated. -- Andrew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Debra Dalgleish's clearing old items from Pivot Table dropdowns
The code loops through all the pivot items in each visible pivot fields,
so it will be slow if there are many pivot items/fields. For pivot tables based on the same pivot cache, you could just run the code on one pivot table, and all the connected pivot tables would be updated. For the manual method to work, you'd have to remove the field from all copies of the pivot table that share the same pivot cache, then refresh the pivot table. '==================== Sub DeleteOldItems_PT() 'gets rid of unused items in PivotTable ' based on MSKB (202232) Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem On Error Resume Next Set pt = ActiveSheet.PivotTables(1) pt.RefreshTable pt.ManualUpdate = True For Each pf In pt.VisibleFields If pf.Name < "Data" Then For Each pi In pf.PivotItems If pi.RecordCount = 0 And _ Not pi.IsCalculated Then pi.Delete End If Next pi End If Next pf pt.ManualUpdate = False pt.RefreshTable Set pt = Nothing End Sub '============================ Andrew wrote: Hi. I was hoping to direct this question directly to Debra as she wrote the code but if anyone else can assist feel free to reply. I used the code on http://www.contextures.com/xlPivot04.html (I'm using Excel 2000) to clear old items from the drop-downs. It took over 30 minutes to complete. On completion I clicked on a drop-down and Excel shut down - frustrating!! I repeated this procedure, saved the file on completion, closed the file and then tried to re-open the file. Again the file caused Excel to shut-down, this time before the file opened. Have you ever experienced this? I also tried the manual way suggested. Although it didn't crash it didn't get rid of the old items. I should also point out there are approximately 10 worksheets within the workbook, with the majority haveing a pivot table on them. I took your (and others) advice and used the same data to use less data. Does this create a problem? Any suggestions would be greatly appreciated. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Debra Dalgleish's clearing old items from Pivot Table dropdown
Hi Debra,
Thanks for the reply. I'm running your code now but it is now 2 hours into it and still going. I break into the code (using ESC) every 15 min or so and you can see it is progressing but gee it takes a long time. Can you please advise why values in the drop-downs are kept. It seems such a logical requirement yet it isn't a feature. The cache must be taking up space in memory, affecting performance. BTW your name is very represented on sites regarding pivot tables. Keep up the good work. Andrew 220208 -- Andrew "Debra Dalgleish" wrote: The code loops through all the pivot items in each visible pivot fields, so it will be slow if there are many pivot items/fields. For pivot tables based on the same pivot cache, you could just run the code on one pivot table, and all the connected pivot tables would be updated. For the manual method to work, you'd have to remove the field from all copies of the pivot table that share the same pivot cache, then refresh the pivot table. '==================== Sub DeleteOldItems_PT() 'gets rid of unused items in PivotTable ' based on MSKB (202232) Dim pt As PivotTable Dim pf As PivotField Dim pi As PivotItem On Error Resume Next Set pt = ActiveSheet.PivotTables(1) pt.RefreshTable pt.ManualUpdate = True For Each pf In pt.VisibleFields If pf.Name < "Data" Then For Each pi In pf.PivotItems If pi.RecordCount = 0 And _ Not pi.IsCalculated Then pi.Delete End If Next pi End If Next pf pt.ManualUpdate = False pt.RefreshTable Set pt = Nothing End Sub '============================ Andrew wrote: Hi. I was hoping to direct this question directly to Debra as she wrote the code but if anyone else can assist feel free to reply. I used the code on http://www.contextures.com/xlPivot04.html (I'm using Excel 2000) to clear old items from the drop-downs. It took over 30 minutes to complete. On completion I clicked on a drop-down and Excel shut down - frustrating!! I repeated this procedure, saved the file on completion, closed the file and then tried to re-open the file. Again the file caused Excel to shut-down, this time before the file opened. Have you ever experienced this? I also tried the manual way suggested. Although it didn't crash it didn't get rid of the old items. I should also point out there are approximately 10 worksheets within the workbook, with the majority haveing a pivot table on them. I took your (and others) advice and used the same data to use less data. Does this create a problem? Any suggestions would be greatly appreciated. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Debra Dalgleish's clearing old items from Pivot Table dropdown
Perhaps the old items were kept so the user could see them, even if they
weren't in the current data. If you enable the 'Show items with no data' option, you could quickly see if any items were not in the new data. This might alert you to a problem with a new download. In Excel 2002, and later versions, you can set the number of missing items that should be retained. Andrew wrote: Hi Debra, Thanks for the reply. I'm running your code now but it is now 2 hours into it and still going. I break into the code (using ESC) every 15 min or so and you can see it is progressing but gee it takes a long time. Can you please advise why values in the drop-downs are kept. It seems such a logical requirement yet it isn't a feature. The cache must be taking up space in memory, affecting performance. BTW your name is very represented on sites regarding pivot tables. Keep up the good work. Andrew 220208 -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't uncheck boxes in Pivot Table dropdowns | Excel Discussion (Misc queries) | |||
Debra Dalgleish's Excel file that displays a comment in a cell instead of a pop-up | Excel Programming | |||
Help with Debra Dalgleish's Code | Excel Programming | |||
Debra Dalgleish: Pivot Table problem | Excel Programming | |||
Clearing Dropdowns In A Worksheet | Excel Programming |