从 VBA 中的 Pivot 过滤器中删除所有内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45552477/
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
Remove all from Pivot filter in VBA
提问by Ollie
I am trying to remove every country in the "countryName" filed list within a pivot table.
我正在尝试删除数据透视表中“countryName”字段列表中的每个国家/地区。
however I get a runtime error 1004, which I believe is down to trying to eliminate the "All" selection. My code is below:
但是我得到一个运行时错误 1004，我认为这是因为试图消除“全部”选择。我的代码如下：
With ActiveSheet.PivotTables("PivotTable1").PivotFields("countryName")
.ClearAllFilters
.EnableMultiplePageItems = True
.PivotItems("All").Visible = False
.PivotItems("GREAT-BRITAIN").Visible = False
End With
Where "countyName" is the field GREAT-BRITAIN is the only country I want displayed
其中“countyName”是字段 GREAT-BRITAIN 是我想要显示的唯一国家
I could do this manually but every time I import new data the country list could differ hence I want to remove "all" then just leaving "GREAT-BRITAIN"
我可以手动执行此操作，但每次导入新数据时，国家/地区列表可能会有所不同，因此我想删除“全部”然后只留下“大不列颠”
Many thanks to anyone who can help
非常感谢任何可以提供帮助的人
回答by jeffreyweir
First up, a couple of alternatives to VBA, in case you haven't considered them. You can use a Slicer to do this with the exact same outcome. Or if you want the user to choose a country from a dropdown, you can use the approach I outline at http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/
首先，有几个 VBA 的替代品，以防你没有考虑过它们。您可以使用切片器以完全相同的结果执行此操作。或者，如果您希望用户从下拉列表中选择一个国家，您可以使用我在http://dailydoseofexcel.com/archives/2014/08/16/sync-pivots-from-dropdown/ 中概述的方法
I take it from the line .EnableMultiplePageItems = True in your code that you are dealing with a PageField? If so, you want to keep .EnableMultiplePageItems set to false, and just change the PageField property.
我从您正在处理 PageField 的代码中的 .EnableMultiplePageItems = True 行中获取它？如果是这样，您希望将 .EnableMultiplePageItems 设置为 false，并更改 PageField 属性。
With ActiveSheet.PivotTables("PivotTable1").PivotFields("countryName")
.ClearAllFilters
.CurrentPage = "GREAT-BRITAIN"
End With
Otherwise you will have to iterate through every item (other than GREAT-BRITAIN) in your PivotTable and set the .visible status to false, which is very slow. (See my post at the following link, as it discusses how to overcome bottlenecks when filtering Pivots with VBA: http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/)
否则，您将不得不遍历数据透视表中的每个项目（GREAT-BRITAIN 除外）并将 .visible 状态设置为 false，这非常慢。（请参阅我在以下链接中的帖子，因为它讨论了如何在使用 VBA 过滤数据透视表时克服瓶颈：http: //dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/)
If your field is NOT a PageField, then you can still do this quickly by setting up a 'Master' PivotTable somewhere out of sight, putting the field of interest in the master as a PageField, connecting the Master PivotTable to PivotTable1 ('Slave') with a Slicer, and using the code snippet above on the hidden Master to set the PageField instantly on one item. The Slicer then syncs the Master with the Slave instantly. I use this approach at VBA to connect slicers (looking for improvements to code)
如果您的字段不是 PageField，那么您仍然可以通过在视线之外的某个地方设置一个“主”数据透视表来快速完成此操作，将感兴趣的字段放在主数据中作为 PageField，将主数据透视表连接到数据透视表 1（“从” ) 与切片器，并使用上面隐藏的 Master 上的代码片段立即在一个项目上设置 PageField。然后切片器立即将主设备与从设备同步。我在VBA 中使用这种方法 连接切片器（寻找代码改进）
