Forum Discussion

RaquelNC's avatar
RaquelNC
Copper Contributor
Oct 21, 2022
Solved

Excel Script Assistance Clear All Slicers - conversion from VBA

Hello Community,

 

If possible, I would like to create an Excel Script that replaces the VBA Code that I have assigned to current button in my Excel Workbook.

 

 

Private Sub ClearFiltersButton_Click()

ActiveWorkbook.SlicerCaches("Slicer_Description_Of_Work_Queue").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_Plan").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_Priority").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_State").ClearManualFilter
ActiveWorkbook.SlicerCaches("Slicer_Recipient").ClearManualFilter

End Sub

 

 

I am new to Excel Script and tried to use the ClearFilter() with the ExcelScript.Slicer unsuccessfully.  I encountered many errors, which I'm sure is related to my lack of knowledge.

 

Your help and time are greatly appreciated.

 

Thanks kindly,

Raquel

Excel for web
Office Scripts
  • JKPieterse's avatar
    JKPieterse
    Oct 24, 2022

    RaquelNC This script works for me:

    function main(workbook: ExcelScript.Workbook) {
	let slicers = workbook.getSlicers();
	for(let i=0;i<slicers.length;i++){
		slicers[i].clearFilters();
	}
}

4 Replies

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Oct 24, 2022

    RaquelNC This script works for me:

    function main(workbook: ExcelScript.Workbook) {
	let slicers = workbook.getSlicers();
	for(let i=0;i<slicers.length;i++){
		slicers[i].clearFilters();
	}
}
    • RaquelNC's avatar
      RaquelNC
      Copper Contributor
      Oct 24, 2022

      JKPieterse That works perfectly.  Thanks for the assistance.  I am working on assigning that script to my button to replace the old VBA.  🙂

      • JKPieterse's avatar
        JKPieterse
        Silver Contributor
        Oct 24, 2022
        There is an option in the script editor menu to add a button to your sheet that calls the script.

Resources