CODEWITHSUNDEEP
excelvba
Glyn
Glyn

Reputation: 1995

PivotTable how to set all items in filter to false?

I have a MS Excel macro that creates a PivotTable. Within the PivotTable I want to filter on multiple items. So first you need to set all items to false and then set the items you want to include to true. So currently I have:

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
    .PivotItems("FXA BI_MH Pre UAT C1").Visible = False
    .PivotItems("FXA BI_MH Pre UAT C2").Visible = False
    .PivotItems("FXA Reg C1").Visible = False
    .PivotItems("MC3").Visible = False
    .PivotItems("PT Cycle 1").Visible = False
    .PivotItems("Regression Test (APO)").Visible = False
    .PivotItems("SIT CR").Visible = False
    .PivotItems("SIT Cycle 2").Visible = False
    .PivotItems("UAT - Data Conv").Visible = False
    .PivotItems("UAT Pre-Test - Additional").Visible = False
    .PivotItems("UAT Pre-Test - Final").Visible = False
    .PivotItems("UAT Pre-Test - Iteration 2").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
    EnableMultiplePageItems = True

However, the "Test Cycle" items change and if a new one is introduced it is automatically included as I have not set it to false. Is there a way to just set all selections to false, something like (which does not work):

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
    .PivotItems("(All)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
    EnableMultiplePageItems = True

With the help of @Josh I have:

ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
        EnableMultiplePageItems = True
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
        For i = 1 To .PivotItems.Count - 1
            If .PivotItems(i).Name <> "UAT Pre-test (FXA)" _
                And .PivotItems(i).Name <> "UAT C2 (FXA)" Then
                .PivotItems(.PivotItems(i).Name).Visible = False
            End If
        Next i
    End With

Upvotes: 2

Views: 35615

Answers (1)

Josh Miller
Josh Miller

Reputation: 620

Rather than listing each item manually, try to turn off the filter with the following:

With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
    For i = 1 To .PivotItems.Count - 1
        .PivotItems(.PivotItems(i).Name).Visible = False
    Next i
End With

Keep in mind, when using the excel interface, you must have at least 1 item selected in order to save the filter.

Excel Pivot Table Filter requires at least one value

Upvotes: 8

Related Questions