How To Filter Column Labels With VBA In An Excel Pivot Table

Question

In Excel I have been able to filter the row labels in a pivot table with this code:

Dim PT as PivotTable
Set PT = ActiveSheet.PivotTables("Pivot1")
With PT
   .ManualUpdate=True
   .ClearAllFiters
   .PivotFields("App").PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:="(Blank)" 
End With

I need to do the same filter for the column labels where my field name is Type.
I tired this:

Dim PT as PivotTable
Set PT = ActiveSheet.PivotTables("Pivot1")
With PT
   .ManualUpdate=True
   .ClearAllFiters
   .PivotFields("App").PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:="(Blank)" 
   .PivotFields("Type").PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:="(Blank)" 

End With

However I get an error, "unable to get the PivotFields property of the PivotTable class" when it hits the below line:

.PivotFields("Type").PivotFilters.Add Type:=xlCaptionDoesNotContain, Value1:="(Blank)"

What syntax do I use to filter the column label?

jeffreyweir jeffreyweir · Accepted Answer · 2017-11-08T03:04:39

There's nothing wrong with that syntax, near as I can tell. Are you sure you have the name right? Best way to check is to fire up the macro recorder, perform the action manually, and see what the resulting code looks like.

Also note that you need to set .ManualUpdate = False when you are done, otherwise your PivotTable won't update when you are done.

How To Filter Column Labels With VBA In An Excel Pivot Table

1 Answers