VBA - Filter within Pivot table

[k3vsmith]

Hello everyone, I'm looking for the best way to do this. Thinking an array...?
I recorded the following macro to do what I want in the pivot table but am looking for a better/streamlined solution.
I have a field in the Pivot table called RES CODE. I want to remove any that start with 9. For my given data this recorded macro works. But...There will be cases when running this macro that there will be more that the below 9#'s. Rather than just manually typing in each of our #'s that start with 9 and adding it to false I was wondering if I could make this alittle smarter and just have it make visible = False on ANY that start with 9?

Current Code:

ActiveSheet.PivotTables("SIOPPivot").PivotFields("RES CODE").CurrentPage = _
        "(All)"
    With ActiveSheet.PivotTables("SIOPPivot").PivotFields("RES CODE")
        .PivotItems("911.MATLS").Visible = False
        .PivotItems("911.SUBCON").Visible = False
        .PivotItems("921.CNTLBR").Visible = False
        .PivotItems("921.ODC").Visible = False
        .PivotItems("921.TRAVEL").Visible = False
        .PivotItems("93.INTEROR").Visible = False
    End With
    ActiveSheet.PivotTables("SIOPPivot").PivotFields("RES CODE"). _
        EnableMultiplePageItems = True
End Sub

 

[Luke M]

Check this out.

Sub Hide9()
Dim pi As PivotItem
Dim pf As PivotField

'Which field?
Set pf = ActiveSheet.PivotTables("SIOPPivot").PivotFields("RES CODE")
pf.EnableMultiplePageItems = True

Application.ScreenUpdating = False
'Loop items
For Each pi In pf.PivotItems
    pi.Visible = (Left(pi.Name, 1) <> "9")
Next pi

Application.ScreenUpdating = True
End Sub

Will hide anything that starts with a 9, show anything else.

 

[k3vsmith]

Awesome. Thank you. I will try this out on Monday and provide feedback.

 

[jeffreyweir]

@Luke M When iterating through the PivotItems collection, you should first set the PivotTable's .ManualUpdate property to TRUE before your PivotItems loop, and set it back to FALSE again when you are done. Otherwise the PivotTable will refresh after each and every PivotItem change. And that can add minutes of unnecessary overhead.

You should also first check whether the PivotItem.Visible status actually needs to change, because it takes far longer to change it than to check it. I have a post on this at http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/

 

[k3vsmith]

Thanks guys, this works great @Luke M.
I'll also give your link a read @jeffreyweir and adjust accordingly.

 

[Luke M]

Thanks for the tip, @jeffreyweir I hadn't realized how much slower that would be. I built the code using a small PT with only 4 items, so it was something I missed.

 

[k3vsmith]

Thanks @Luke M
I now need to include blanks as well. So any that start with 9 and any that are labeled "(blank)". I tried duplicating the pii visible line to add a line for (blank) but that errored. Any help is appreciated. Also, there may be cases where there isnt a (blank). Not sure if that fact would change the code?

 

[Luke M]

Taking into account Jeff's advice, here's the new sub

Sub Hide9()
Dim pI As PivotItem
Dim pF As PivotField
Dim pT As PivotTable

'Which table?
Set pT = ActiveSheet.PivotTables("SIOPPivot")
'Which field?
Set pF = pT.PivotFields("RES CODE")
pT.ManualUpdate = False
pF.EnableMultiplePageItems = True

Application.ScreenUpdating = False
'Loop items
For Each pI In pF.PivotItems
    'Hide if Item starts with 9 or is blank
    If Left(pI.Name, 1) = "9" Or pI.Name = "(blank)" Then
        'Only change field visibility if needed
        If pI.Visible Then
            pI.Visible = False
        End If
    Else
        If pI.Visible = False Then
            pI.Visible = True
        End If
    End If
Next pI
pT.ManualUpdate = True
Application.ScreenUpdating = True
End Sub

 

[k3vsmith]

Perfect, thanks again!