# Speed up looping through slicers and .selected true if slicer item name matches input words

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?

Invariants, variables which don't change in a loop, should be set before entering the loop. Additionally, if you want to store the value of a Boolean expression, just store the expression's return value:

Sub SearchSlicer()
Dim oSc As SlicerCache
Dim oSi As SlicerItem
Dim searchword As Variant

searchword = InputBox("Enter a searchword")
searchword = "*" & UCase(searchword) & "*"
Set oSc = ThisWorkbook.SlicerCaches("Slicer_SEARCH_WORDS")

For Each oSi In oSc.SlicerItems
oSi.Selected = oSi.Name Like searchword
Next oSi
End Sub


But I doubt this will speed up the loop much. Most likely the 'like' operator is where most of the time is spent (and I don't see any alternative to it).

• I think, in this particular case, the like might be better off with instr - this of course is not compatible with your new structure. – Raystafarian Aug 22 '18 at 4:25
• Thanks for the response! It did speed up the code a little bit. This will be very helpful for other tables with less rows in and I will just have to find a way to work around the table with many rows. – C L Aug 22 '18 at 12:54