SOLVED

Excel Script Assistance Clear All Slicers - conversion from VBA

Copper Contributor

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

4 Replies
best response confirmed by RaquelNC (Copper Contributor)
Solution

@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();
	}
}

@Jan Karel Pieterse That works perfectly.  Thanks for the assistance.  I am working on assigning that script to my button to replace the old VBA.  :)

There is an option in the script editor menu to add a button to your sheet that calls the script.

@Jan Karel Pieterse I saw that after I added it to my script list and renamed it for the workbooks that I was working on.  Thanks for reaching out to let me know.  I greatly appreciate all the tips and tricks. 

1 best response

Accepted Solutions
best response confirmed by RaquelNC (Copper Contributor)
Solution

@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();
	}
}

View solution in original post