Pivottable, remove obsolete items

[Tommy Bak]

Hi
does anyone know how to get rid of obsolete / missing items. ?
If the database is updated and the pivottable is refreshed, the dropdownboxes still contains items that has been removed from the database.
Is it possible to update these items


Regards
Tommy Bak

 

[JPM]

Can you explain a little bit more what kind of drop-down you are referring to?

JPM

 

[Tommy Bak]

Ok, i did maybe not make it clear.
I'm using excel XP /win 2000
The dropdownboxes are the dropdownbox on the pivottable.
(to choose which item to see row-wise)
If the pivottable is updated, these boxes are not updated, but does still contain information (rowfields) from the previous version.
The database eg. contains SalesRep, month, and sale in $ and i make the pivottable on this database with SalesRep as Rowfield, month as columnfield and Sale as data.
If I then deletes a salesrep from the database and updates the pivottable the deleted salesrep is still represented in the dropdownboxes.


regards
Tommy Bak

 

[Greg Truby]

I know exactly what you're talking about and it's annoying as heck. For page fields you can double click the page field "button" and tell it to hide certain items. But for row and column fields, I've never been able to figure out a way to do something similar. I'll be following this thread with interest.

 

[JPM]

Ok, now I know what you are talking about. Sorry, I am no help there, have the same problem.

JPM

 

[tusharm]

AFAIK, there's no nice and simple solution.

It can't be done through the GUI. I believe there's a way of doing it programmatically, but, at the moment, I can't research it. Alternatively, delete the PT and create it again. Neither is an appealing option, I know, but...

 

[Tommy Bak]

Hi
programmatically would be ok for me, but deleting the PivotTable is (as tusharm wrote) not an appealing option.

Trying to refresh the underlying PivotCache (VBA) does not work and it seems like I can't refresh/update PivotItems

Regards
Tommy Bak

 

[Greg Truby]

I've got to get home, but this appears to work if I go in by hand and uncheck the Show All box and then check one box for a record that I know still exists...

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> DeletePivotItemTest()
    <SPAN style="color:#00007F">Dim</SPAN> pi1 <SPAN style="color:#00007F">As</SPAN> PivotItem
    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> pi1 <SPAN style="color:#00007F">In</SPAN> ActiveSheet.PivotTables("PivotTable2").PivotFields("Rep").PivotItems
        <SPAN style="color:#00007F">If</SPAN> pi1.Visible = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN> pi1.Delete
    <SPAN style="color:#00007F">Next</SPAN> pi1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[Tommy Bak]

Ok Greg
Now we have a semiautomatic solution.
Lets hope that it can be evolved a little

regards
Tommy Bak

 

[Tommy Bak]

HI
I got this working now, but it still need a little work to be generic

Sub DeletePivotItemTest()
Dim pi1 As PivotItem
On Error Resume Next
For Each pi1 In ActiveSheet.PivotTables("PivotTable1").PivotFields("SalesRep").PivotItems
If pi1.RecordCount = 0 Then pi1.Delete
Next pi1
End Sub