Filtering pivot table with values in VBA

Question

Im trying to filter a pivot table column (GP#) from an array (shortened in this example).

The filter does work but when I get to a number (in this case 83292) that is not in the filter, the excel crashes with the error :

Runtime error 1004, application-defined or object-defined error

Is there a way of checking if a number/name etc is in the filter and if it is then apply to filter?

My code:

vGP = Array("83041", "83327", "83292")
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GP#")
    .PivotItems(1).Visible = True

    ' below code ensure pivot table filter does not cause error by not having anything in the filter

    For i = 2 To .PivotItems.Count
        .PivotItems(i).Visible = False
        If .PivotItems(i).Visible Then .PivotItems(i).Visible = False
    Next i

    ' goes through array and adds any value in array
    For Each i In vGP
        .PivotItems(i).Visible = True
    Next i
    On Error GoTo 0

Can anyone please help ensuring that the values in the array can be added to the filter and values in the array that are not present in the pivot table are ignored

So this question seems to be asking the exact same thing as your previous question at https://stackoverflow.com/questions/45718045/pivotfields-multiple-filter/45726720#45726720 that I already answered with some very efficient code. — jeffreyweir, Sep 21 '17 at 21:54
Furthermore, your code above has multiple design flaws in it, both of which I programmed around with the original answer. The line `If .PivotItems(i).Visible Then .PivotItems(i).Visible = False` is completely redundant, and the next loop needs to be wrapped in an `On Error Resume Next`. What is the point of us answering your questions if you completely disregard the previous answer that works perfectly? — jeffreyweir, Sep 21 '17 at 22:01

score 1 · Answer 1 · answered Sep 21 '17 at 12:27

Try the code below to find if a certain array element is found within the PivotField you use named GP#.

Dim PvtTbl As PivotTable
Dim PvtFld As PivotField
Dim MatchFound As Boolean, i As Long

' set the Pivot Table
Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")

' set the Pivot Field
Set PvtFld = PvtTbl.PivotFields("GP#")

MatchFound = False ' reset flag
For i = 1 To PvtFld.PivotItems.Count ' loop through all pivot items
    If PvtFld.PivotItems(i).Name = vGP(1) Then ' check if the second array element is found in one of the Pivot items
        MatchFound = True ' raisw flag
        Exit For
    End If
Next i

If MatchFound Then
    PvtFld.PivotItems(i).Visible = True ' apply filter if the array element found
End If

Filtering pivot table with values in VBA

1 Answers1