Multiple Value Filter Macro Issue

[Mark W]

Hello,

As within a pivot table you are not able to have multiple value filters, I am trying to create a macro to run this and new to macros. I used macro recorder for each individual filter I want to do and tried to just combine them but not working.

I get Object doesn't support this property or method error message when I try to run the macro. Below is a snip of my pivot table. I want to filter the Field "concat" which are the total rows (0003.0001-AUD TOTAL) to not include Oracle Bal Base between -10,000 and 10,000 as deemed insignificant and then also want to filter to remove anything where the Number Line value equals 0. Below the excel clip is the macro I have so far, if someone can please let me know where I am going wrong that would be greatly appreciated.



See below code:

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

With pvt.PivotFields("Concat")
.ClearAllFilters
.AllowMultipleFilters = True
.PivotFilters.Add Type:=xlValueIsNotBetween, DataField:=ActiveSheet.PivotTables("PivotTable9" _
).PivotFields("Oracle Bal Base"), Value1:=-10000, Value2:=10000
.PivotFilters.Add Type:=xlValueDoesNotEqual, DataField:=ActiveSheet.PivotTables("PivotTable9" _
).PivotFields("Number Line"), Value1:=0
End With

End Sub

 

[Chihiro]

Short answer is that you can't.

Whatever filter condition that is applied last to value field will override the previous.

Alternate method is to read/loop through PivotItems to find Row Label that meets criteria (based on value) and filter those items out on Row Label and not on value field.

However, it is bit convoluted to accomplish this.

Depending on your data structure, you may be able to clean up source data using PowerQuery before adding it to data model and then create PivotTable.

Edit: Another method is to add helper column to source data as filter condition operator. And filter based on that column.