Removing Dates from Slicer via VBA

Copper Contributor

Hi,

I am using Excel 365; I created a slicer to filter my pivot table by date. I want to remove any dates before 1/1/2023 from the slicer. Is this possible?

 

The code that I have tried (it filters the dates from the pivot table but does not remove it from the slicer)

Sub FilterSlicer()
Dim sc As SlicerCacheScreenshot 2023-07-25 103303.png
Dim si As SlicerItem
Set sc = ActiveWorkbook.SlicerCaches("Slicer_Start_Month_Year")
For Each si In sc.SlicerItems
If InStr(si.Name, "2022") > 0 Or si.Name = "(blank)" Then
si.Selected = False
Else
si.Selected = True
End If
Next si
End Sub

4 Replies

@Jesusismygrace 

Add a helper column to your data and check whether Date is >=01/01/2023.

Move the field into the pivot table or create a slicer and filter on TRUE.

 

Thank you, below is the help column that I was using. Is there any way to keep the blanks from showing up in the slicer?
=IF(ISBLANK([@[Start Date]]), UNICHAR(160), IF([@[Start Date]] > TODAY(), UNICHAR(160), EOMONTH([@[Start Date]], 0)))
This is my revised helper, and it works, thank you. Is there any way to remove the blanks from the slicer now?
=IF(ISBLANK([@[Start Date]]), UNICHAR(160), IF(OR([@[Start Date]] > TODAY(), [@[Start Date]] < DATE(2023, 1, 1)), UNICHAR(160), EOMONTH([@[Start Date]], 0)))

@Jesusismygrace 

That is not what I suggested.