Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide items with no data in pivot table | Excel Discussion (Misc queries) | |||
Pivot Table: How do I hide calculated items that result in zero? | Charts and Charting in Excel | |||
How can I hide zero rows in a pivot table with calculated items? | Excel Programming | |||
Pivot Table: Hide Items Poperty | Excel Programming | |||
Auto Hide Pivot Table Items | Excel Programming |