arrays - 如何使用数组选择多个切片器项目？

Question

我正在尝试在切片器中为数据透视表选择多个项目。

我创建了一个包含所有应该选择的项目的数组。我的代码只选择一项。

For cnt = UBound(Visible_Both_Years) To 0 Step -1

'filled array
MsgBox Visible_Both_Years(cnt)

'Loop through filter 
With k
    For Each l In .PivotItems
        Select Case l.Name
            Case Is = Visible_Both_Years(cnt)
                l.Visible = True
            Case Else
                l.Visible = False
        End Select
    Next

End With

我是VBA的新手。

标签： arraysexcelvbapivotslicers

Answer 1

There's no need to loop through your array, try...

'Loop through filter
With k
    .ClearAllFilters 'clear any existing filters
    For Each l In .PivotItems
        If IsError(Application.Match(l.Name, Visible_Both_Years, 0)) Then
            l.Visible = False
        End If
    Next
End With

Hope this helps!