Forum Discussion

Jesusismygrace's avatar
Jesusismygrace
Copper Contributor
Jul 25, 2023

Removing Dates from Slicer via VBA

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 SlicerCache
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

excel
Macros and VBA
office 365

4 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jul 25, 2023

    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.

     

    • Jesusismygrace's avatar
      Jesusismygrace
      Copper Contributor
      Jul 25, 2023
      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's avatar
      Jesusismygrace
      Copper Contributor
      Jul 25, 2023
      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)))

Resources