Fast way to hide all pivot items in VBA?
 

Is there a faster way to hide the pivot items in a pivot field than
using a FOR EACH...NEXT statement? It takes roughly 1 second per pivot
item to change it's visibility.

I'm using the following code:

Code:
--------------------
For Each pi In ActiveSheet.PivotTables("PivotTable1").PivotFields ("BILL_TO_CUST_NAME").PivotItems
pi.Visible = False
Next
--------------------


_The_pivot_field_has_a_couple_hundred_items_in_it_ and only 5 need to
be visible; the rest need to be hidden. I'm using code to filter the
table several ways and save each result for another report. The
original report is automatically created by IT and they said that's the
way the report is going to stay.

Suggestions?


--
Air_Cooled_Nut


------------------------------------------------------------------------
Air_Cooled_Nut's Profile: http://www.excelforum.com/member.php...o&userid=15730
View this thread: http://www.excelforum.com/showthread...hreadid=501870

Fast way to hide all pivot items in VBA?
 

application.screenupdating=false
application.calculation=xlmanual
'do your stuff
application.screenupdating=true
application.calculation=xlautomatic ' (check these constants!)

You should consider adding an error handler to ensure the settings are
restored if your code could raise an error.

Tim


--
Tim Williams
Palo Alto, CA


"Air_Cooled_Nut"
wrote in
message news:Air_Cooled_Nut.21r44m_1137454801.063@excelfor um-nospam.com...

Is there a faster way to hide the pivot items in a pivot field than
using a FOR EACH...NEXT statement? It takes roughly 1 second per pivot
item to change it's visibility.

I'm using the following code:

Code:
--------------------
For Each pi In
ActiveSheet.PivotTables("PivotTable1").PivotFields ("BILL_TO_CUST_NAME").Pivo
tItems
pi.Visible = False
Next
--------------------


_The_pivot_field_has_a_couple_hundred_items_in_it_ and only 5 need to
be visible; the rest need to be hidden. I'm using code to filter the
table several ways and save each result for another report. The
original report is automatically created by IT and they said that's the
way the report is going to stay.

Suggestions?


--
Air_Cooled_Nut


------------------------------------------------------------------------
Air_Cooled_Nut's Profile:
http://www.excelforum.com/member.php...o&userid=15730
View this thread: http://www.excelforum.com/showthread...hreadid=501870

Fast way to hide all pivot items in VBA?
 

Thanks Tim. I did turn off screen updating but didn't even think abou
automatic calculations. However, adding that really didn't help much.
I did a count on the pivot items in the pivot field and it was 7999!
Talk about frickin' stupid :eek

--
Air_Cooled_Nu

-----------------------------------------------------------------------
Air_Cooled_Nut's Profile: http://www.excelforum.com/member.php...fo&userid=1573
View this thread: http://www.excelforum.com/showthread.php?threadid=50187