Macro - Filter Pivot Table multiple does not contain

t33p33 · Jun 14th 2013

I am trying to write a macro that will filter a pivot table. I want to filter out anything that contains one of two values; "Hosting", and "Infrastructure". I am getting an error on the following macro. It will only filter out the first value. I have tried multipe versions and cannot seem to find a way to filter out anythign containing two different values. Any assistance would be appreciated.

Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")
With PTitle
.ClearAllFilters
.PivotFilters.Add xlCaptionContains, , "Hosting"
.PivotFilters.Add xlCaptionContains, , "Infrastructure"
End With
End Sub

I error out when I get to the second filter. I can get this to work with one filter, but not to filter out two different values.

t33p33 · Jun 14th 2013

Re: Macro - Filter Pivot Table multiple does not contain

Also tried this with the same issue of only filtering out the first value and then erroring out.

Sub Filter()
ActiveSheet.PivotTables("PivotTable3").PivotFields("Project Title"). _
PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:= _
"Hosting"
ActiveSheet.PivotTables("PivotTable3").PivotFields("Project Title"). _
PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:= _
"Infrastructure"
End Sub

Herbds7 · Jun 14th 2013

Re: Macro - Filter Pivot Table multiple does not contain

Excel 2010 PivotTable
Each Type of PivotFilter (xlCaption..., xlValue...)
can have only one Value1 (no OR condition)
Alternative methods:
1. PivotField("x").PivotItems("y").Visible=True/False
2. Helper Column in source data
3. PowerPivot with Calculated Column

t33p33 · Jun 14th 2013

Re: Macro - Filter Pivot Table multiple does not contain

Herbsds7, Thanks for the input.

I now have a modified script as follows, but seem to be erroring out on the line

Code

[PTitle.PivotItems(i).Visible = False/CODE]


[CODE]
Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")
Application.ScreenUpdating = False
With PTitle
    .ClearAllFilters
    For i = 1 To PTitle.PivotItems.Count
        If InStr(1, PTitle.PivotItems(i), "Hosting", vbTextCompare) > 0 _
        Or InStr(1, PTitle.PivotItems(i), "Infrastructure", vbTextCompare) > 0 _
        Then
            PTitle.PivotItems(i).Visible = True
        Else
            PTitle.PivotItems(i).Visible = False
        End If
    Next i
End With
End Sub

Display More

Herbds7 · Jun 15th 2013

Re: Macro - Filter Pivot Table multiple does not contain

Excel 2010, Table, PivotTable
PivotItems.Visible macro
and PowerPivot example.
http://www.mediafire.com/downl…3n060qs7fcd/06_14_13.xlsm
Also see this book:
Excel 2007 VBA, Programmer's Reference by Green, Bovey & Alexander

t33p33 · Jun 15th 2013

Re: Macro - Filter Pivot Table multiple does not contain

Solved! No need for Power pivot and definitely possible. If anyone else out there would like to have multiple lable filters here you go:

Code

Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")
Application.ScreenUpdating = False
With PTitle
    .ClearAllFilters
    For i = 1 To .PivotItems.Count
        If InStr(1, .PivotItems(i), "Hosting", vbTextCompare) > 0 _
        Or InStr(1, .PivotItems(i), "Infrastructure", vbTextCompare) > 0 _
        Then
            PTitle.PivotItems(i).Visible = False
        End If
    Next i
End With
End Sub

Display More

t33p33 · Jun 15th 2013

Re: Macro - Filter Pivot Table multiple does not contain

Also speed it up by turning off manual updates

Code

Sub testFilter()
Dim PTitle As PivotField
Set PTitle = ActiveSheet.PivotTables(1).PivotFields("Project Title")


With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End with


ActiveSheet.PivotTables(1).ManualUpdate = True


With PTitle


    .ClearAllFilters
    For i = 1 To .PivotItems.Count
        If InStr(1, .PivotItems(i), "Hosting", vbTextCompare) > 0 _
        Or InStr(1, .PivotItems(i), "Infrastructure", vbTextCompare) > 0 _
        Then
            PTitle.PivotItems(i).Visible = False
        End If
    Next i
End With


With Application
    .ScreenUpdating = true
    .Calculation = xlCalculationAutomatic
End with


ActiveSheet.PivotTables(1).ManualUpdate = false
End Sub

Display More

Macro - Filter Pivot Table multiple does not contain

Participate now!

Share