Sub SearchSlicer() Dim oSc As SlicerCache Dim oSi As SlicerItem Dim searchword As Variant searchword = InputBox("Enter a searchword") Set oSc = ThisWorkbook.SlicerCaches("Slicer_SEARCH_WORDS") For Each oSi In oSc.SlicerItems If oSi.Name Like "*" & UCase(searchword) & "*" Then oSi.Selected = True Else: oSi.Selected = False End If Next End Sub
So I have a pivot table with two columns - a column for a verb and a column for search words containing synonyms/similar phrases. For example, if a word in the first column is 'broke', the second column will contain the list 'fell apart, broken, split'.
Running the above code asks the user for a search word which will be used to loop through the search word column and filter the search word pivot table and slicers.
This search word slicer is linked to first column slicer as the search word slicer is not visible on the input sheet.
The reason for using slicers is because I have another code for when the user clicks on a slicer choice, it automatically pastes it into the active cell.
I have a table with 350 rows and this code is already taking very long to run. How can I make this more optimal?