vba Pivotfields 多重过滤器
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45718045/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivotfields multiple filter
提问by Ollie
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).
我有下面的代码，我正在尝试让数据透视表的数据透视字段部分显示 3 个国家/地区（法国、比利时和卢森堡）。每次更新表格时，国家列表都会扩大和缩小（但法国、比利时和卢森堡仍然存在）。
'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?
有人能帮忙吗？
采纳答案by jeffreyweir
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
