vba 数据透视表如何将过滤器中的所有项目设置为false？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29356631/
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
PivotTable how to set all items in filter to false?
提问by Glyn
I have a MS Excel macro that creates a PivotTable. Within the PivotTable I want to filter on multiple items. So first you need to set all items to false and then set the items you want to include to true. So currently I have:
我有一个创建数据透视表的 MS Excel 宏。在数据透视表中，我想过滤多个项目。因此，首先您需要将所有项目设置为 false，然后将要包含的项目设置为 true。所以目前我有：
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
.PivotItems("FXA BI_MH Pre UAT C1").Visible = False
.PivotItems("FXA BI_MH Pre UAT C2").Visible = False
.PivotItems("FXA Reg C1").Visible = False
.PivotItems("MC3").Visible = False
.PivotItems("PT Cycle 1").Visible = False
.PivotItems("Regression Test (APO)").Visible = False
.PivotItems("SIT CR").Visible = False
.PivotItems("SIT Cycle 2").Visible = False
.PivotItems("UAT - Data Conv").Visible = False
.PivotItems("UAT Pre-Test - Additional").Visible = False
.PivotItems("UAT Pre-Test - Final").Visible = False
.PivotItems("UAT Pre-Test - Iteration 2").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
EnableMultiplePageItems = True
However, the "Test Cycle" items change and if a new one is introduced it is automatically included as I have not set it to false. Is there a way to just set all selections to false, something like (which does not work):
但是，“测试周期”项目会发生变化，如果引入了新项目，它会自动包含在内，因为我没有将其设置为 false。有没有办法将所有选择设置为false，例如（不起作用）：
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
.PivotItems("(All)").Visible = False
End With
ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
EnableMultiplePageItems = True
With the help of @Josh I have:
在@Josh 的帮助下，我有：
ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle"). _
EnableMultiplePageItems = True
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
For i = 1 To .PivotItems.Count - 1
If .PivotItems(i).Name <> "UAT Pre-test (FXA)" _
And .PivotItems(i).Name <> "UAT C2 (FXA)" Then
.PivotItems(.PivotItems(i).Name).Visible = False
End If
Next i
End With
回答by Josh Miller
Rather than listing each item manually, try to turn off the filter with the following:
与其手动列出每个项目，不如尝试使用以下命令关闭过滤器：
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Test Cycle")
For i = 1 To .PivotItems.Count - 1
.PivotItems(.PivotItems(i).Name).Visible = False
Next i
End With
Keep in mind, when using the excel interface, you must have at least 1 item selected in order to save the filter.
请记住，使用 excel 界面时，您必须至少选择 1 个项目才能保存过滤器。
