AutoFilter column containing value from dropdown selection

[yanzers]

Hi all,

I'm trying to create a macro that auto-filters a specific column based on a selection in a drop-down.

With the script below I am able to filter based on the dropdown selection, however, it only gives me back "equal" matches. In my case, the column that needs to be filtered contains cells with multiple values. So what I need is a text filter based on "contain".

Here's how it should work:

• The drop-down is in cell D3
• The table is range "D5:N300"
• The column that needs to be filtered is in cell L5
• When selecting a value in D3 (e.g. New Zealand) I want to apply an auto-filter on L5 for all cells containing "New Zealand" including cells that contain e.g. New Zealand and Australia

Any help with this is much appreciated!

This is the code I currently have (and works) to filter for exact matches:

Sub Filter_CellValue1()
ActiveSheet.Range("D5:N300").Autofilter field:=9, Criteria1:=Cells(3, 4).Value
End Sub

 

[Fluff]

How about

Sub Filter_CellValue1()
ActiveSheet.Range("D5:N300").Autofilter field:=9, Criteria1:="*" & Cells(3, 4).Value & "*"
End Sub

 

[jasonb75]

I have a feeling that might be too simple @Fluff, although it could just be me overthinking again.

My way of looking at it is that it needs to work with 4 criteria to prevent false inclusions or omissions in the results:-
[D4]
[D4]&" *"
"* "&[D4]
"* "&[D4]&" *"
With the wildcards as you have used them a criteria of "Niger" in D4 could return "Nigeria" as an incorrect result.

Borrowing some code from @Domenic's reply in an old thread and matching it to the OP's ranges and criteria hopefully this will do the trick.

Using VBA to create wildcard filter for multiple criteria

Hey guys, I'm new to using VBA, but I now know autofilter will only allow 2 sets of criteria for searching, and will not work w/wildcards (*). Here's the array function I tried to use that obviously didn't work. ActiveSheet.Range("$A$10").CurrentRegion.AutoFilter Field:=2, Criteria1:= _...

www.mrexcel.com

Option Explicit

Sub test()


    Dim dicCriteria As Object
    Dim vData As Variant
    Dim i As Long
    Dim crit As String
    
    Set dicCriteria = CreateObject("Scripting.Dictionary")
    dicCriteria.CompareMode = 1 'vbTextCompare


    With ActiveSheet
        crit = .Range("D4").Value
        If .FilterMode Then .AutoFilterMode = False
        With .Range("D5:N300")
            vData = .Columns(9).Cells.Value
            For i = 2 To UBound(vData, 1) 'exclude headers and start from the second row of data
                If Not dicCriteria.Exists(vData(i, 1)) Then
                    Select Case True
                        Case vData(i, 1) Like crit
                            dicCriteria(vData(i, 1)) = ""
                        Case vData(i, 1) Like crit & " *"
                            dicCriteria(vData(i, 1)) = ""
                        Case vData(i, 1) Like "* " & crit
                            dicCriteria(vData(i, 1)) = ""
                        Case vData(i, 1) Like "* " & crit & " *"
                            dicCriteria(vData(i, 1)) = ""
                    End Select
                End If
            Next i
            If dicCriteria.Count > 0 Then
                .AutoFilter field:=9, Criteria1:=dicCriteria.Keys, Operator:=xlFilterValues
            Else
                MsgBox "No records found.", vbInformation
            End If
        End With
    End With

    Set dicCriteria = Nothing

End Sub

 

[Fluff]

I have a feeling that might be too simple @Fluff,

You could be right, but without knowing what the OP's data is like, or what exactly is a "match" there is no way of knowing.

 

[yanzers]

How about

Sub Filter_CellValue1()
ActiveSheet.Range("D5:N300").Autofilter field:=9, Criteria1:="*" & Cells(3, 4).Value & "*"
End Sub

Hi folks, thanks for looking into this.

This suggestion by Fluff works a charm! Thanks a mil for helping out a noob

Do any of you have a suggestion of how I can make this script trigger every time a new value is selected in the dropdown in D3?

Thanks a mil!

 

[Fluff]

You could put it into a worksheet_change event.

 

[jasonb75]

This suggestion by Fluff works a charm!

Just a quick check that you caught my comment about partial matches. It's a common problem that people often don't realise can happen until they start seeing random errors in their results.

In another thread yesterday, a member had the problem the criteria "HR" returning results that contained the word "threat" because the criteria is found in that word.

If that is what you want to happen then @Fluff's code is exactly what you need, however if you need it to exclude such results then you will need a different method such as the one in my earlier reply. Noting that you used countries in your example, the example of "Niger" also returning rows that contain "Nigeria" is the only one that comes to mind, although there may be others that I can't remember (geography was never one of my strong points ).

 

[yanzers]

Just a quick check that you caught my comment about partial matches. It's a common problem that people often don't realise can happen until they start seeing random errors in their results.

In another thread yesterday, a member had the problem the criteria "HR" returning results that contained the word "threat" because the criteria is found in that word.

If that is what you want to happen then @Fluff's code is exactly what you need, however if you need it to exclude such results then you will need a different method such as the one in my earlier reply. Noting that you used countries in your example, the example of "Niger" also returning rows that contain "Nigeria" is the only one that comes to mind, although there may be others that I can't remember (geography was never one of my strong points ).

Thanks for calling that out! I doublechecked and for my data set it doesn't seem to apply, so it works