使用 vba 取消选择数据透视表中的所有项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13768110/
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
Deselect all items in a pivot table using vba
提问by Chris2015
Can some quicly explain the way to deselect all items in a newly created pivot table so that I can go back and select only one or two items? I tried the following:
有人可以快速解释在新创建的数据透视表中取消选择所有项目的方法，以便我可以返回并仅选择一两个项目吗？我尝试了以下方法：
.PivotItems("(Select All)").Visible = False
Thanks.
谢谢。
回答by Daniel
This is probably the closest you can get to what you want:
这可能是您最接近您想要的：
Dim i As Long
.PivotItems(1).Visible = True
For i = 2 To .PivotItems.Count
.PivotItems(i).Visible = False
Next
This will make the very first option the only selected option (assuming this is within a with that points to the pivotfield). If you know what you want before hand... modify accordingly.
这将使第一个选项成为唯一选择的选项（假设它在指向数据透视域的 a 内）。如果你事先知道你想要什么......相应地修改。
回答by gilad
I've found that looping through each data item takes a lot of time, what you can do if you want to filter on a single item in a pivot without looping through all items is use the following code:
我发现遍历每个数据项需要很多时间，如果您想过滤数据透视中的单个项目而不遍历所有项目，您可以使用以下代码：
ActiveSheet.PivotTables("Your Pivot Name").PivotFields("Your Field Name").ClearAllFilters
ActiveSheet.PivotTables("Your Pivot Name").PivotFields("Your Field Name").PivotFilters.Add _
Type:=xlCaptionEquals, Value1:="Your string here"
this is basically a label filter but it worked for me.
这基本上是一个标签过滤器，但它对我有用。
回答by bonCodigo
Check the following out. Select data for specific field name. Please do note that you have to at least select one item by default. And also do not forget that if you want to hide items, Only contiguous items in a PivotTable Field can be hidden.Perhaps at page load, or worksheet open or any of your other sub trigger, you could select a particular items to be selected based on a specific field. Then allow your code to proceed with anything else.
检查以下内容。选择特定字段名称的数据。请注意，默认情况下您必须至少选择一项。并且不要忘记，如果您想隐藏项目，则只能隐藏数据透视表字段中的连续项目。也许在页面加载、工作表打开或任何其他子触发器时，您可以根据特定字段选择要选择的特定项目。然后让您的代码继续执行其他任何操作。
Sub specificItemsField()
Dim pf As PivotField
Dim pi As PivotItem
Dim strPVField As String
strPVField = "Field Name"
Set pt = ActiveSheet.PivotTables(1)
Set pf = pt.PivotFields(strPVField)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
pf.AutoSort xlManual, pf.SourceName
For Each pi In pf.PivotItems
pi.Visible = True
Next pi
pf.AutoSort xlAscending, pf.SourceName
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
回答by asun
This is how I do for custom filter selection. May be slower due to double looping.
这就是我如何进行自定义过滤器选择。由于双循环，可能会更慢。
Dim toSelect(1 To 3) As String
toSelect(1) = "item1"
toSelect(2) = "item2"
toSelect(3) = "item3"
For Each pvItem In objField.PivotItems
For Each st In toSelect
If pvItem.Value = st Then
pvItem.Visible = True
Exit For
Else
pvItem.Visible = False
End If
Next
Next
回答by Sergio Cristiá
Well.
好。
Because you have not how to hide all, because, always you need to have 1 item visible
因为你没有如何隐藏所有，因为，你总是需要有 1 个项目可见
I do this:
我这样做：
I start hiding the first field, and before go to the next, i show all fields i need visible, then, i go to the secont item, and hide, and again show all items i want, and so on. Then, always will be visible any field, and wont have error.
我开始隐藏第一个字段，然后在转到下一个字段之前，显示我需要可见的所有字段，然后转到第二个项目，然后隐藏，再次显示我想要的所有项目，依此类推。然后，任何字段都将始终可见，并且不会出错。
After the loop, i again try to show all fields i want.
循环后，我再次尝试显示我想要的所有字段。
With ActiveSheet.PivotTables("TablaD2").PivotFields("Entity")
使用 ActiveSheet.PivotTables("TablaD2").PivotFields("Entity")
Dim i As Long
For i = 1 To .PivotItems.Count
.PivotItems(i).Visible = False
.PivotItems("ARG").Visible = True
.PivotItems("BRL").Visible = True
.PivotItems("GCB").Visible = True
.PivotItems("MEX").Visible = True
Next
.PivotItems("ARG").Visible = True
.PivotItems("BRL").Visible = True
.PivotItems("GCB").Visible = True
.PivotItems("MEX").Visible = True
End With
