VBA Pivot table filter running too slow

[lynnsong986]

Hello,

I have the codes below that loops through a column in rowfield in a pivot table and filters out everything but a given value that is provided through input box.

the codes took forever to run because the list is very long, is there a faster way of coding this?

For Each PTItm In pvt.PivotFields("Address").PivotItems
    If PTItm.Value = Building Then
        PTItm.Visible = True
    Else
        PTItm.Visible = False
    End If
Next PTItm 

thanks,
Lynn

[XLent]

Untested but you might try applying Manual Update

With pvt
    .ManualUpdate = True
    For Each PTItm In .PivotFields("Address").PivotItems
        With PTItm
            .Visible = (.Value = Building)
        End With
    Next PTItm
    .ManualUpdate = False
End With

note: I'm assuming Building is a variable - else this should be encased within quotations and you might want to remove case sensitivity (c/o UCase on both sides)

[lynnsong986]

Thank you so much XLent, it runs much faster now, very much appreciated.

[XLent]

No problem; for reference the above is akin to using the "Defer Layout Update" option when you update a Pivot table layout via the XL Pivot Field List dialog.

https://docs.microsoft.com/en-us/off...e.manualupdate

[lynnsong986]

great very helpful tips thanks again!