Conditional setting for PivotItems in given Pivotfield

Hi

I have a range that lists a number of PivotItems. I have drafted some code that cycles through the list and, if a match is found, sets the corresponding PivotItem's .visible property to True. If the match isn't found then the .visible property is set to False.

The code is as follows:

[INDENT]Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
 
    Set pt = Sheets("CostReport").PivotTables("PivotTable2")
    pt.PivotFields("Office").PivotItems("(blank)").Visible = True
 
    For y = 1 To 67
    If Rng.Offset(y, x).Value <> "" Then
    pt.PivotFields("Office").PivotItems(Rng.Offset(y, 0).Value).Visible = True
    Else
    pt.PivotFields("Office").PivotItems(Rng.Offset(y, 0).Value).Visible = False
    End If
    Next y
    pt.PivotFields("Office").PivotItems("(blank)").Visible = False
 
[/INDENT]

[INDENT]
The macro executes successfully but unfortunately is painfully slow (due in main I think because the PivotTable recalculates itself after each change in the selected PivotItems).

Is anyone aware of any enhancements to this code that could speed up this execution? Perhaps if the .visible properties could be simultaneously set for all fields at once rather than one at a time?

Cheers

Chris
[/INDENT]

Re: Conditional setting for PivotItems in given Pivotfield

Thanks both.

I'm on 2007 so unfortunately I couldn't check the latter solution, however the pt.ManualUpdate tweak has seen a pretty hefty improvement in speed so thanks for that

Cheers

Chris