Autofilter a table in another sheet based on selected cell value where autofilter needs to be 'contains'

[crackod93]

hi all, firstly i'm not that familiar with VBA so i appreciate any and all guidance i can get from this community with this query I have.

So i've referenced this solution 'https://stackoverflow.com/questions/35231790/clicking-a-hyperlink-in-excel-to-set-autofilter-on-a-different-sheet#', which takes the value from the cell which is double-clicked and then autofilters the target sheet & column to display matches for that value. The only issue is that where the target sheet/column contains additional values in the same cell the autofilter skips it because i think its looking for EXACT matches rather than something like a CONTAINS:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If ActiveCell.Column = 3 Then
Sheet1.ListObjects("target").Range.AutoFilter Field:=17, Criteria1:=ActiveCell.Value
Sheet1.Activate
End If
End Sub

Can anyone please help me to somehow make the autofilter do a CONTAINS the value for matching rather than a having the cells be an EXACT match?

many thanks, David

 

[Fluff]

Try

Criteria1:="*" & ActiveCell.Value & "*"

 

[crackod93]

Try

Criteria1:="*" & ActiveCell.Value & "*"

wow that was super speedy, i've tried that and works like a charm - thank you so much!!!

 

[Fluff]

You're welcome