Excel Pivot Tables Hidden Items

[cheerio]

My problem is arising with an Excel 2000 pivot table. (It started life in Excel 97 but I am trying to work with it in 2000).

We have a field dimensioned as a page field - Manager.

This has values say: Fred Bloggs, John Smith, Prince Harry, ...

My boss who I prepared this pivot table for wants to see what the data would look like without the part of our empire managed by John Smith. So he double clicks on the Manager field and selects John Smith to hide him.

The next day he comes to me and says the pivot table is not working because it does not tie up with data from another source. After investigation, we track down that he has hidden a manager and forgotten.

Foolishly, perhaps, I volunteered that I could probably write a VBA procedure that would unhide all such items.

Now if I record the unhiding I get:

With ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("Manager&quot
.PivotItems("John Smith&quot.Visible = True
End With

The good news is it works. The bad news is it takes about a second per manager using a 600 mhz PC if I loop through all the managers. There are maybe 30 managers. The really bad news is that in addition there are a dozen other fields such a DIVISION, PERIOD, ACCOUNTANT,... and some of these might have a hundred items in them. So basically my simple code is not much good unless a PC magazine wants a nice slow benchmark.

Plan B - Excel help has an example that looks very attractive along the lines of:

For each pvtItem in pvtTable.PivotFields("Manager&quot.HiddenItems
' some action here
Next pvtItem

The trouble is that it does not work. Basically, most items are coming up in the HiddenItems collection even if they have not been hidden. At the very least just having a Page Field set to All seems to make everything hidden.

Has anyone got ideas as to how I can make this work?

Ken

 

[xlbo]

Hi, havn't tested this but try something like:

Set pvtTable = activesheet.pivottables(1)
For i = 1 to pvtTable.PivotFields.count
For each pvtItem in pvtTable.PivotFields(i)
pvtItem.visible = true
Next pvtItem
next i

HTH
~Geoff~

 

[cheerio]

That's what I was trying but the inner step in the loop seems to take about a second per item on a reasonably fast machine and a typical table will have 1000+ items.

I was trying to use the HiddenItems attribute to cut down the number of items to be reset but the attribute appears not to work. That is my real problem.

Ken

 

[xlbo]

Hi Ken - this seems to work - Excel97 tho
The lines that begin with ' are commented out - uncomment if you want to see how many items it's looping thru but I hid 15 items in various different fields and got an answer of 15 so it seems to only loop through the hidden items

Sub UnhideHiddenItems()
Application.ScreenUpdating = False
'x = 0
activesheet.pivottables(1).pivotcache.refresh
For i = 1 To ActiveSheet.PivotTables(1).PivotFields.Count
For Each pvtitem In ActiveSheet.PivotTables(1).PivotFields(i).HiddenItems
pvtitem.Visible = True
'x = x + 1
Next pvtitem
Next i
Application.ScreenUpdating = True
'MsgBox x
End Sub

HTH
~Geoff~

 

[cheerio]

Geoff

Thanks for your thoughts.

I shall try to return to the problem next week and will text out your thoughts.

Ken