Pivotfields multiple filter

Question

I have the code below and i'm trying to have my pivotfields part of the pivot table show 3 countries (France, Belgium and Luxembourg). The list of countries expands and contracts each time the table is updated (but France, Belgium and Luxembourg remain).

'delete all filters for country
  With ActiveSheet.PivotTables("PivotTable1").PivotFields("countryName")
  .ClearAllFilters
  .CurrentPage = "FRANCE"
  .PivotItems("BELGIUM").Visible = True
  .PivotItems("LUXEMBOURG").Visible = True

  End With

This doesn't work, there are now issue with the code (errors) that arise but Belgium and Luxembourg don't appear on the filtered list

Can anyone help with this?

Your current approach uses the .CurrentPage property, which is only relevant if a) your pivotfield is a PageField (i.e. appears in the FILTERS pane of the PivotTable fields list) and b) if the "Select Multiple Items" option in the field's dropdown is unchecked. The PageField property is used to set a PivotTable to display just one PivotItem. You can't use it to display more than one item. — jeffreyweir, Aug 17 '17 at 04:03
Can you confirm where abouts in the PivotTable the PivotFIeld is? i.e. is it in the Filters area, or the Rows or Columns area? — jeffreyweir, Aug 17 '17 at 04:05

jeffreyweir · Accepted Answer · 2017-09-21T23:04:39.953

This code should do what you need. To learn more about filtering PivotTables quickly, check out my blogpost on the subject.

Option Explicit

Sub FilterPivot()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim i As Long
Dim vItem As Variant
Dim vCountries As Variant

Set pt = ActiveSheet.PivotTables("PivotTable1")
Set pf = pt.PivotFields("CountryName")

vCountries = Array("FRANCE", "BELGIUM", "LUXEMBOURG")

pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed

With pf

    'At least one item must remain visible in the PivotTable at all times, so make the first
    'item visible, and at the end of the routine, check if it actually  *should* be visible        
    .PivotItems(1).Visible = True

    'Hide any other items that aren't already hidden.
    'Note that it is far quicker to check the status than to change it.
    ' So only hide each item if it isn't already hidden
    For i = 2 To .PivotItems.Count
        If .PivotItems(i).Visible Then .PivotItems(i).Visible = False
    Next i

    'Make the PivotItems of interest visible
    On Error Resume Next 'In case one of the items isn't found
    For Each vItem In vCountries
        .PivotItems(vItem).Visible = True
    Next vItem
    On Error GoTo 0

    'Hide the first PivotItem, unless it is one of the countries of interest
    On Error Resume Next
    If InStr(UCase(Join(vCountries, "|")), UCase(.PivotItems(1))) = 0 Then .PivotItems(1).Visible = False
    If Err.Number <> 0 Then
        .ClearAllFilters
        MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Pivot, so I have cleared the filter"
    End If
    On Error GoTo 0

End With

pt.ManualUpdate = False

End Sub

Thanks very much for this, i cant quite get this to work, the code doesnt have any errors and it looks good to me but the outcome is such that all the countries in the list are still selected (apart from the 1st one, which i see is planned in the code) — Ollie, Aug 17 '17 at 11:03
i know you say it takes longer but works anyway, thanks very much, you are helping me out a great deal recently — Ollie, Aug 17 '17 at 12:59
No problem. Can you elaborate on what you mean by ".PivotItems(i).Visible = False" ? And can you also elaborate on where abouts in the PivotTable the PivotFIeld is? i.e. is it in the Filters area, or the Rows or Columns area? — jeffreyweir, Aug 17 '17 at 19:33

Pivotfields multiple filter

1 Answers1

Linked