正在尝试缩小此VBA脚本的范围,但不必列出。我是一个基本的宏用户。我如何才能让一个selected = True,rest = False,而不必列出所有160个?
With ActiveWorkbook.SlicerCaches("Slicer_Organization")
.SlicerItems("900110 - Danish Bemidji ").Selected = True
.SlicerItems("900101 - Arabic Bemidji").Selected = False
.SlicerItems("900105 - Chinese Callaway 1st Half").Selected = False
.SlicerItems("900106 - Chinese Callaway 2nd Half").Selected = False
.SlicerItems("900115 - Finnish Bemidji ").Selected = False
.SlicerItems("900120 - French Bemidji 1st Half ").Selected = False
.SlicerItems("900121 - French Bemidji 2nd Half ").Selected = False
发布于 2018-07-25 11:57:10
过滤SlicerItems可能很棘手,因为至少有一项必须始终保持可见。
此代码显示如何筛选名为vSelection的数组上的切片器。要根据需要进行修改,只需更改vSelection = Array("B", "C", "E")
行,使其包含感兴趣的项目。
Option Explicit
Sub FilterSlicer()
Dim slr As Slicer
Dim sc As SlicerCache
Dim si As SlicerItem
Dim i As Long
Dim vItem As Variant
Dim vSelection As Variant
Set sc = ActiveWorkbook.SlicerCaches("Slicer_ID")
vSelection = Array("B", "C", "E")
For Each pt In sc.PivotTables
pt.ManualUpdate = True 'Stops PivotTable from refreshing after each PivotItem is changed
Next pt
With sc
'At least one item must remain visible in the Slicer at all times, so make the first
'item visible, and at the end of the routine, check if it actually *should* be visible
.SlicerItems(1).Selected = 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 .SlicerItems.Count
If .SlicerItems(i).Selected Then .SlicerItems(i).Selected = 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 vSelection
.SlicerItems(vItem).Selected = 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(vSelection, "|")), UCase(.SlicerItems(1).Name)) = 0 Then .SlicerItems(1).Selected = False
If Err.Number <> 0 Then
.ClearAllFilters
MsgBox Title:="No Items Found", Prompt:="None of the desired items was found in the Slicer, so I have cleared the filter"
End If
On Error GoTo 0
End With
For Each pt In sc.PivotTables
pt.ManualUpdate = False
Next pt
End Sub
发布于 2018-07-25 05:09:29
您可以使用For Each
循环遍历SlicerCache
中的所有SlicerItems
:
Dim si as SlicerItem
For Each si In ActiveWorkbook.SlicerCaches("Slicer_Organization").SlicerItems
si.Selected = (si.Name = "900110 - Danish Bemidji")
Next si
该(si.Name = "900110 - Danish Bemidji")
将返回一个True
或False
,并相应地设置您正在迭代的SlicerItem
的Selected
属性。
发布于 2021-06-17 03:33:27
Sub ExcelGuruVG()
Dim myval As String
myval = "VG"
Dim sli as SlicerItem
with Activeworkbook.slicercaches("Slicer_SLICERNAME")
for Each sli in SlicerItems
if sli.Name = myval then
sli.select = true
Else
sli.selected = False
End if
Next sli
End with
End Sub
https://stackoverflow.com/questions/51507498
复制相似问题