Using an Array to Filter a Pivot Table Using VBA

Question

I'm new to arrays and how to use them correctly. I keep getting MyArray(i,1) = subscript out of range and it highlights the rows, where I put in those asterisks.

I want to minimize line count and make it more efficient, so I can pull the same array into different pivot to filter.

Sub Macro1()     

Dim MyArray() As Variant
Dim i As Integer

    'Populate the array.
    MyArray = Array("I1", "I2", "I3")

    'Filter based off array values
    For i = 1 To UBound(MyArray)
*       ActiveSheet.PivotTables("PivotTable1").PivotFields("letters "). _
*       CurrentPage = MyArray(i, 1)
    Next i

End Sub

My old code that I'm trying to make more efficient is:

Sub Macro1()      

    Sheets("NonDomestic").PivotTables("PivotTable1").PivotFields("letters "). _
        CurrentPage = "(All)"
    With Sheets("NonDomestic").PivotTables("PivotTable1").PivotFields( _
        "dir sales ship cust cot ")

        .ClearAllFilters
        .PivotItems("A1").Visible = False
        .PivotItems("B1").Visible = False
        .PivotItems("C1").Visible = False
        .PivotItems("C2").Visible = False
        .PivotItems("D1").Visible = False
        .PivotItems("D2").Visible = False
        .PivotItems("D3").Visible = False
        .PivotItems("D4").Visible = False
        .PivotItems("D5").Visible = False
        .PivotItems("D6").Visible = False
        .PivotItems("D7").Visible = False
        .PivotItems("E1").Visible = False
        .PivotItems("F1").Visible = False
        .PivotItems("F2").Visible = False
        .PivotItems("F3").Visible = False
        .PivotItems("F4").Visible = False
        .PivotItems("F5").Visible = False
        .PivotItems("F6").Visible = False
        .PivotItems("F7").Visible = False
        .PivotItems("G1").Visible = False
        .PivotItems("G2").Visible = False
        .PivotItems("G3").Visible = False
        .PivotItems("G4").Visible = False
        .PivotItems("G5").Visible = False
        .PivotItems("H1").Visible = False
        .PivotItems("H3").Visible = False
        .PivotItems("H4").Visible = False
        '.PivotItems("I1").Visible = False
        '.PivotItems("I2").Visible = False
        '.PivotItems("I3").Visible = False
    End With

On Error GoTo 0

Are arrays 0- or 1-based? (0-based!) It matters because your array is sized according to I1 and I2, no? SO is not a great tutorial site, so maybe check "vba array" web search results and see if you are doing what you should be. — user1531971
– user1531971, Commented Feb 15, 2019 at 18:05

Asger · Accepted Answer · 2019-02-15 17:34:13Z

1

CurrentPage is intended to show only 1 PivotItem of your filter.
If you have a list of visible/unvisible PivotItems, then set them individually visible, addressing them by their name or by their index.

Private Sub PivotFilterTest()
    Dim pf As PivotField
    Dim myArray() As Variant
    Dim i As Long

    myArray = Array("I1", "I2", "I3")
    Set pf = ActiveSheet.PivotTables("PivotTable1").PivotFields("letters ")
    With pf
        .ClearManualFilter
        .EnableMultiplePageItems = True
        For i = LBound(myArray) To UBound(myArray)
            .PivotItems(myArray(i)).Visible = False
        Next i
    End With
End Sub

edited Feb 15, 2019 at 17:34

answered Feb 15, 2019 at 17:27

Asger

3,8773 gold badges14 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patch Over a year ago

You are the man! I appreciate such a detailed and quick response!

Asger Over a year ago

@Patch Thanks :) And if you get further and hassle with the "blank" page, then please refer to this: stackoverflow.com/a/54420639/10908769

Collectives™ on Stack Overflow

Using an Array to Filter a Pivot Table Using VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related