excel - 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?

Answer 1

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