Excel VBA - Pivot table filter multiple criteria

Question

I am trying to filter a pivot table with multiple criteria. I've check other posts, but I am getting the error

AutoFiler method of Range class failed

when running:

Range("g41").Select
Selection.AutoFilter field:=1, Criteria1:=Array( _
    "101", "103"), Operator:=xlFilterValues

The following works, but there are quite a long number of items to filter true/false

With ActiveSheet.PivotTables("PivotTable3").PivotFields("Value")
    .PivotItems("101").Visible = True
    .PivotItems("103").Visible = True
    .PivotItems("105").Visible = False
End With

Is there a more effective way?

If you try to filter a piviot table using the normal method you will find that it is greyed out when you try to do this. Piviot tables are already filtered which is why the code you recorded works but the code you wrote doesnt — Mr.Burns, Feb 28 '17 at 10:44
Thanks Mr.Burns. But is there a way to filter the pivot with a range of values. I have over 400 PivotItems and I only need 15 of those visible — Selrac, Feb 28 '17 at 11:34
@Selrac try the code below to filter out large sets, use an array — Shai Rado, Feb 28 '17 at 11:34

score 12 · Accepted Answer · answered Feb 28 '17 at 11:34

You can try the code below:

Option Explicit

Sub FilterPivotItems()

Dim PT          As PivotTable
Dim PTItm       As PivotItem
Dim FiterArr()  As Variant

' use an array to select the items in the pivot filter you want to keep visible
FiterArr = Array("101", "105", "107")

' set the Pivot Table
Set PT = ActiveSheet.PivotTables("PivotTable3")

' loop through all Pivot Items in "Value" Pivot field
For Each PTItm In PT.PivotFields("Value").PivotItems
    If Not IsError(Application.Match(PTItm.Caption, FiterArr, 0)) Then ' check if current item is not in the filter array
        PTItm.Visible = True
    Else
        PTItm.Visible = False
    End If
Next PTItm

End Sub

score 0 · Answer 2 · answered Jan 24 '20 at 06:40

These issue has a very simple solution.

Just use method to select items via “SLICER” (and VBA code).

For more details use record macro option.

Code example

ActiveWorkbook.SlicerCaches("Slicer_RS_YM").SlicerItems(“Item1Name”).Selected = True ‘ include ActiveWorkbook.SlicerCaches("Slicer_RS_YM").SlicerItems(“Item2Name”).Selected = False ‘ exclude

Czeskleba · Answer 3 · 2020-02-05T09:33:20.247

I was just trying to do a similar thing, and got an error about not being able to adjust visibility.

PTItm.Visible = False

Looking into it, it had something to do with the kind of query (OLAP or not I think or maybe because I set the pivot up as a table view). Anyway, I had to adjust Shai's great idea a little to make it work. I thought I'd share this to save others, who come across this thread in the future, some time fiddling with odd errors. The key was to use another array and .VisibleItemsList.

Sub FilterPivotItems()

Dim PT          As PivotTable
Dim PTItm       As PivotItem
Dim FiterArr()  As Variant
Dim NewFilterList() As Variant
ReDim Preserve NewFilterList(0) 'I still haven't figured out how to avoid this double dim'ing, sorry

' use an array to select the items in the pivot filter you want to keep visible
FiterArr = Array("[POSched].[Inspection Plan].&", "[POSched].[Inspection Plan].&[DEFAULT]") '2 Items here, the first one means empty i.e. ""

' set the Pivot Table
Set PT = ThisWorkbook.Sheets("Prisma").PivotTables("SequenceOverview")

' loop through all Pivot Items in "Value" Pivot field
For Each PTItm In PT.PivotFields("[POSched].[Inspection Plan].[Inspection Plan]").PivotItems
    If IsError(Application.Match(PTItm.Value, FiterArr, 0)) Then ' check if current item is not in the filter array
        NewFilterList(UBound(NewFilterList)) = PTItm.Value 'Make a new array to use later
        ReDim Preserve NewFilterList(UBound(NewFilterList) + 1)
    End If
Next PTItm

ThisWorkbook.Sheets("Prisma").PivotTables("SequenceOverview").PivotFields("[POSched].[Inspection Plan].[Inspection Plan]").VisibleItemsList = NewFilterList

End Sub

Edit: I forgot to mention that this code assumes, that no filters were applied before. This works fine for me but may not be helpful for everyone.

Brockomatic · Answer 4 · 2023-02-14T16:53:41.653

0

In response to Czeskleba's issue. I got the same error as well, but all I did was add a "clear filter" command and never had the issue again.

ActiveSheet.PivotTables("YOUR-TABLE-NAME").PivotFields("VALUE-TO-FILTER").ClearAllFilters

Works like a charm.

EDIT: Insert that after the Dim statements and before your first FilterArr statement.

edited Feb 14 '23 at 16:53

answered Feb 14 '23 at 16:52

Brockomatic

1
1

Excel VBA - Pivot table filter multiple criteria

4 Answers4

Linked