Pivot Table filter out only 1 option

Question

I'm having a problem with removing one option from the filter in my Pivot Table, if I record a macro and try to apply changes with only one of ~20 options removed, Excel pops up a message:

Too many line continuations!

Seems like it is trying to declare each of the possible filter options by name, just like here:

ActiveSheet.PivotTables("PivotTable1").PivotFields( _
    "[Product Component].[(c) Segment 4].[(c) Segment 4]").VisibleItemsList = Array _
    ("[Product Component].[(c) Segment 4].&[31558]", _
    "[Product Component].[(c) Segment 4].&[315516]", _
    "[Product Component].[(c) Segment 4].&[3152027]", _
    "[Product Component].[(c) Segment 4].&[3152028]")

Is it possible to remove only one option and show the rest ~20 with one command?

But I want to use all the possible options except one. I don't know the code, so I'm recording a macro. Is there a code for showing all filter options except the specified? — Artur, Jan 23 '18 at 15:02
pnuts: The OP is trying to filter a large OLAP PivotTable, using the code that the Macro recorder spat out when they did it manually. They can't necessarily fix the issue by simply removing line continuations. What they need to do is to create an array programmatically. I'll whip up some code in due course. — jeffreyweir, Jan 23 '18 at 19:09
Weird: There is a HiddenItemsList option in intellisence that you'd think would be perfect for this challenge, but I can't get it to work...it throws an error when I change .VisibleItemsList to .HiddenItemsList on a few test items in my small OLAP PivotTable. — jeffreyweir, Jan 23 '18 at 19:15
Ahh: You've got to set pf.CubeField.IncludeNewItemsInFilter = True first. — jeffreyweir, Jan 23 '18 at 19:18

jeffreyweir · Answer 1 · 2018-01-24T23:17:11.913

Great question. Because this is an OLAP PivotTable, the key is to set the PivotField's CubeField.IncludeNewItemsInFilter Property to either TRUE or FALSE depending on what you want to do. See https://msdn.microsoft.com/en-us/vba/excel-vba/articles/pivotfield-includenewitemsinfilter-property-excel

Let's say we're interested in these two items:

[Product Component].[(c) Segment 4].&[31558]
[Product Component].[(c) Segment 4].&[315516]

If you want only those two things to be visible, set the PivotField's CubeField.IncludeNewItemsInFilter Property to FALSE, and then feed an array of things that should be visible to pf.VisibleItemsList, like this:

Sub ShowOLAPItems()
'
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("[Product Component].[(c) Segment 4].[(c) Segment 4]")
pf.CubeField.IncludeNewItemsInFilter = FALSE 'This is the default property
pf.VisibleItemsList = Array("[Product Component].[(c) Segment 4].&[31558]", _
    "[Product Component].[(c) Segment 4].&[315516]")

End Sub

If you want everything except those two things to be visible, set the PivotField's CubeField.IncludeNewItemsInFilter Property to TRUE, and then feed an array of things that should be visible to pf.HidenItemsList, like this:

Sub HideOLAPItems()
'
Dim pt As PivotTable
Dim pf As PivotField

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("[Product Component].[(c) Segment 4].[(c) Segment 4]")
pf.CubeField.IncludeNewItemsInFilter = TRUE 
pf.HiddenItemsList = Array("[Product Component].[(c) Segment 4].&[31558]", _
    "[Product Component].[(c) Segment 4].&[315516]")

End Sub

I tried running your code, but it doesn't work... My VBA pops up a message "Application-defined or object-defined error" at the last line. — Artur, Jan 24 '18 at 08:34
Ahh...I think I had FALSE and TRUE around the wrong way in my two examples. I've amended the code, so copy it and try again. Note that if you have a Slicer connected to the field, this won't work: Slicers are incompatible with the pf.CubeField.IncludeNewItemsInFilter = TRUE setting for some reason. — jeffreyweir, Jan 24 '18 at 23:18
Unfortunately my pivot table uses slicers, so VBA pops up an error at this statement... Is there nothing I can do? — Artur, Jan 29 '18 at 14:38
Okay...we'll have to create the array programmatically. No problem. I'll write some code shortly, and post it as another answer. — jeffreyweir, Jan 29 '18 at 17:29

Brandon Barney · Answer 2 · 2018-01-23T19:21:08.510

If you want to remove just one item from the filter, then do exactly that:

Dim Table as PivotTable
Set PivotTable = YourPivotTable

Dim Field as PivotField
Set Field = Table.PivotFields("YourFieldName")

With Field
    .EnableMultiplePageItems = True
    .ClearAllFilters
End With

Field.PivotItems("YourItemName").Visible = False

Since clearing all filters means all items will be visible, we can then remove each item one by one.

Worth noting that if you are doing this a lot, you should set Table.ManualUpdate = False until you are done with changing filters, and then Table.ManualUpdate = True once you are done. This will prevent it from updating every time a filter changes (which can be very time consuming).

EDIT: In the event that you must pass an array, then just build one like so:

Dim ItemToHide as String
ItemToHide = "YourItemToHide"

' Assumes only one is being hidden
Dim VisibleItems(1 to Field.PivotItems.Count - 1) as Variant

Dim Item as PivotItem
For each Item in Field.PivotItems
    If Item.Value <> ItemToHide Then
        Dim Count as Long
        Count = Count + 1

        VisibleItems(Count) = Item.Value
    End If
Next

Table.VisibleItemsList = VisibleItems

Brandon: the OP's PivotTable is an OLAP PivotTable, meaning your approach won't work. For OLAP tables, you've got to feed the .VisibleItemsList an array. You can't selectively show/hide PivotItems like you can with normal PivotTabes. — jeffreyweir, Jan 23 '18 at 19:06

Pivot Table filter out only 1 option

2 Answers2

Linked