Using an Array to check PivotItems.Name <> Array Then PivotItems.Visible = False

user_6835996 0
Viewed 321

I am attempting to use an array to check the filtered pivot items in the pivot table to see if it should be visible or not. Currently I have it hard coded with the code below. However, I would like to use an Array when I have more then one item to check against the filter.

The goal is to only filter on the items needed.

Supply = Array("X Marketplace", "Y Marketplace")

With PivFid
    For i = 1 To .PivotItems.Count
        If .PivotItems(i).Name <> "X Marketplace" Then .PivotItems(i).Visible = False
    Next i
End With

This is something I am trying to accomplish but I'm not sure how to loop through the array against the filtered items.

With PivFid
    For i = 1 To .PivotItems.Count
        If .PivotItems(i).Name <> Supply Then .PivotItems(i).Visible = False
    Next i
End With
vba excel
1 Answers

You can use Application.Match to check it. Combined with IsError in front of it, it will check if a current PivotItem name is not found within the entire Supply array).

Code

Supply = Array("X Marketplace", "Y Marketplace")

With PivFid
    For i = 1 To .PivotItems.Count
        If IsError(Application.Match(.PivotItems(i).Name, Supply, 0)) Then .PivotItems(i).Visible = False
    Next i
End With

Just in case you need to show the other ones:

With PivFid
    For i = 1 To .PivotItems.Count
        If IsError(Application.Match(.PivotItems(i).Name, Supply, 0)) Then
            .PivotItems(i).Visible = False
        Else
            .PivotItems(i).Visible = True
        End If
    Next i
End With
user_63443630
Related