CODEWITHSUNDEEP
vbapivot-table
user1093111
user1093111

Reputation: 1111

How to get the opposite of VisibleItemsList - HiddenItemsList (1004)

So, I've written some VB to filter a pivot table based on a range. 

Worksheets("Collections By Timekeepers").PivotTables("Collections By Timekeepers").PivotFields(1).VisibleItemsList = arrFilters

Now I need to do the same but with HiddenItemsList, however, I'm getting a 1004 "Application-defined or object-defined error" despite being an OLAP cube.

Worksheets("Collections By Timekeepers").PivotTables("Collections By Timekeepers").PivotFields(2).HiddenItemsList = arrFilters

Is there a work around, or another way to exclude items?

Upvotes: 1

Views: 2091

Answers (1)

jeffreyweir
jeffreyweir

Reputation: 4834

You need to set .CubeField.IncludeNewItemsInFilter to True in order to do this. Suggest you check out my two recent answers on this:

This answer gives you the syntax: Pivot Table filter out only 1 option

Note that you can't use this method on PageFields. But here's a workaround: This answer gives you a function to filter OLAP PivotTables no matter what kind of field they are: how to set PivotField.HiddenItemsList property's value if CubeField.Orientation = xlPageField

Upvotes: 1

Related Questions