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

0
votes

i'm not that familiar with VBA, so i've referenced this solution 'Clicking a hyperlink in Excel to set autofilter on a different sheet', however what i need the autofilter to do is to look within the target column on the sheet for cells which contain the value selected

this is what im using which is fine for autofiltering where the exact value matches the target, but if the target contains multiple values in the field then it doesnt include them in the filter:

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

I think i need to somehow change the 'Sheet1.ListObjects("target").Range.AutoFilter Field:=17, Criteria1:=ActiveCell.Value' part to look in Field 17 where value contains, eg:

if field 17 looked link this and the ActiveCell.Value i selected was 'AA' the VBA im using would only show 2 entries instead of the 4 rows where it actually is

Field 17 (the below should show in a column layout not row btw)

AA, AB AA AA AC AF, AA, AB

many thanks!

1
vbaautofilter

1 Answers

0
votes

solved!

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