CODEWITHSUNDEEP
excelvbafilterpivot
T_Higgins
T_Higgins

Reputation: 11

Excel VBA - Pivot Table with Multiple Value Filters

Is there a way in Excel VBA to allow for multiple value filters in a Pivot Table? I'm trying to filter values for >0 AND for Bottom Ten items. In the sample code below (generated by recording a Macro), the second filter step overrides the first. If multiple value filters were supported, it seems I would only need to add the Boolean logic AND between these two expressions to get the product of both filters. Please suggest any changes to the code or let me know if this is not supported. Thanks!

Sub Multiple_Value_Filters()

    ActiveSheet.PivotTables("PivotTable1").PivotFields("Full Name").PivotFilters.Add _       
        Type:=xlValueIsGreaterThan, _
        DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Days"), _
        Value1:=0
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Full Name").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Full Name").PivotFilters.Add _
        Type:=xlBottomCount, _
        DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Days"), _
        Value1:=10
End Sub

Upvotes: 1

Views: 31050

Answers (2)

Axel
Axel

Reputation: 2725

I know this question is already quite old, but I stumbled upon it recently and nothing was really working for me. I could only use one filter for the pivot table. For the second or third I always got Run Time Errors. However, I found a simple workaround, which I will describe below. I hope other people coming here with similar issues might find it helpful.

Step 1: Add Helper Columns to the data source with a new heading and any constant value in every row. (You need one helper column per extra filter. If you want to use 3 filters, you need two helper columns) enter image description here

Step 2: Add the Helpercolumn attributes to your row-fields of the pivot table. enter image description here

Step 3: Choose the tabular layout where all row attributes are in one row. enter image description here

Step 4: Now you can apply different filters, one for each attribute in the row-field. This will yield the same result as if you use multiple filters for the "Heading 1". If you don't like the extra columns, you can also just hide them away.

Step 5: If you now apply this to VBA, the code could look like this:

Dim pvt As PivotTable
Set pvt = ActiveSheet.PivotTables("PivotTable1")

ActiveSheet.PivotTables("PivotTable1").AllowMultipleFilters = True

With pvt.PivotFields("Heading 1")
    .ClearAllFilters
    .PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Heading 2"), Value1:=2
End With
With pvt.PivotFields("Help 1")
    .ClearAllFilters
    .PivotFilters.Add2 Type:=xlValueIsSmallerThan, DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Heading 2"), Value1:=11
End With
With pvt.PivotFields("Help 2")
    .ClearAllFilters
    .PivotFilters.Add2 Type:=xlValueIsGreaterThan, DataField:=ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Heading 3"), Value1:=4
End With

I hope this is useful.

Upvotes: 1

Archias
Archias

Reputation: 383

I can't really tell what your pivottable should be doing without seeing some sample data but I believe you should be able to do what you are trying to. Try messing around with this:

Sub Multiple_Value_Filters()
    Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables("PivotTable1")

    With pvt.PivotFields("Full Name")
        .ClearAllFilters
        .PivotFilters.Add Type:=xlValueIsGreaterThan, DataField:=pvt.PivotFields("Days"), Value1:=0
        .PivotFilters.Add Type:=xlBottomCount, DataField:=pvt.PivotFields("Days"), Value1:=10
    End With
End Sub

Have discovered the PivotTable Option to allow multiple filters, however it didn't quite work for me even when it would work as I manually did it. For whatever reason Excel seems to not like the code. This functionality only works for Excel 2007 PivotTables and newer but I am running mine from Excel 2010 so I am not sure what the issue is here.

Sub Multiple_Value_Filters()
    Dim pvt As PivotTable
    Set pvt = ActiveSheet.PivotTables("PivotTable1")

    With pvt.PivotFields("Full Name")
        .ClearAllFilters
        .AllowMultipleFilters = True ' This is the main key to getting this to work but mine still errors out whenever I add the 2nd filter. 
        .PivotFilters.Add Type:=xlValueIsGreaterThan, DataField:=pvt.PivotFields("Days"), Value1:=0
        .PivotFilters.Add Type:=xlBottomCount, DataField:=pvt.PivotFields("Days"), Value1:=10
    End With
End Sub

Upvotes: 1

Related Questions