SiGill1979
New Member
- Joined
- Oct 13, 2016
- Messages
- 3
I have a table in my spreadsheet with 2 slicers.
When an item is selected in Slicer 1 I want to count the number of slicer items in Slicer 2.
The code I have counts all the items in slicer 2 rather than just the filtered ones. How can I count just the filtered ones?
This is my code
Sub Slicers()
Application.ScreenUpdating = False
Dim strWeek As String
Dim intSlicerCount As Integer
Dim i As Integer
Application.Volatile
strWeek = InputBox("Please input the Arrival Week required")
intSlicerCount = ActiveWorkbook.SlicerCaches("Slicer1").SlicerItems.Count
ActiveWorkbook.SlicerCaches("Slicer1").ClearManualFilter
For i = 1 To intSlicerCount
With ActiveWorkbook.SlicerCaches("Slicer1")
If i = strWeek Then
.SlicerItems(i).Selected = True
Else
.SlicerItems(i).Selected = False
End If
End With
Next i
intSlicerCount = ActiveWorkbook.SlicerCaches("Slicer2").VisibleSlicerItems.Count
MsgBox intSlicerCount
Application.ScreenUpdating = True
End Sub
When an item is selected in Slicer 1 I want to count the number of slicer items in Slicer 2.
The code I have counts all the items in slicer 2 rather than just the filtered ones. How can I count just the filtered ones?
This is my code
Sub Slicers()
Application.ScreenUpdating = False
Dim strWeek As String
Dim intSlicerCount As Integer
Dim i As Integer
Application.Volatile
strWeek = InputBox("Please input the Arrival Week required")
intSlicerCount = ActiveWorkbook.SlicerCaches("Slicer1").SlicerItems.Count
ActiveWorkbook.SlicerCaches("Slicer1").ClearManualFilter
For i = 1 To intSlicerCount
With ActiveWorkbook.SlicerCaches("Slicer1")
If i = strWeek Then
.SlicerItems(i).Selected = True
Else
.SlicerItems(i).Selected = False
End If
End With
Next i
intSlicerCount = ActiveWorkbook.SlicerCaches("Slicer2").VisibleSlicerItems.Count
MsgBox intSlicerCount
Application.ScreenUpdating = True
End Sub