View Full Version : Efficient way to run sub if change via slicer is detected

11-05-2016, 10:25 AM
Hi all

First of I would like to thank all contributors for all the interesting post on here.

Second - could some one have a look and help me come up with a better/faster solution.

The solution below works - as such - but is far far far to heavye/slow. It can take several minutes to go through the rutine when I reset the slicer/remove filter.

I need something that can call the hideNodata sub if change due to slicer is detected but is fast enough to work for both single slicer item selection af when you remove the slicer"filter".

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim slItem As SlicerItem

For Each slItem In ActiveWorkbook.SlicerCaches("Test1").SlicerItems
If slItem.Selected = False Then
Call AutofilterHideNoData
End If
Next slItem

End Sub

Sub AutofilterHideNoData()
ActiveSheet.ListObjects(1).Range.AutoFilter field:=1, Criteria1 _
:="<>No data", Operator:=xlAnd

End Sub

Hope you can help. Best Regards