Filter based on multiple criteria separated by commas

clariinc · Feb 7th 2017

Hi,

I have two existing codes that:

1) Filters based on multiple criteria (keyed into 'Raw' tab, B3 to F3)
2) One of the criteria is a dropdown data validation list, which i have used a code to enable multiple selections (see attached file 'Raw' tab, cell D3)

The issue is that the filter does not work when D3 uses multiple criteria, as excel does not recognize the commas as separate criteria. Is there a way to modify my Filtering macro such that i can overcome this issue?

It's possible that I may be selecting multiple options in the D3 dropdown and not just 2 values.

Thanks!

**StephenR** · Feb 7th 2017

Re: Filter based on multiple criteria separated by commas

Try this.

Code

Sub FilterMe()


Dim v, n As Long


With Sheet1
    Application.EnableEvents = False
    .Range("J4:N4").Resize(50).ClearContents
    v = Split(.Range("D3"), ", ")
    n = UBound(v) + 1
    .Range("L3").Resize(n).Value = Application.Transpose(v)
    If n > 1 Then
        .Range("J4:K4").Resize(n - 1).Value = .Range("J3:K3").Value
        .Range("M4:N4").Resize(n - 1).Value = .Range("M3:N3").Value
    End If
    .Range("B6").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange _
        :=Range("J2:N2").Resize(n + 1), Unique:=False
    Application.EnableEvents = True
End With


End Sub

Display More

clariinc · Feb 8th 2017

Re: Filter based on multiple criteria separated by commas

Hmm sorry but it does not seem to work when i replaced the FilterMe code with the above. The filter still produces no results when run with multiple criteria in cell D3/L3

**StephenR** · Feb 8th 2017

Re: Filter based on multiple criteria separated by commas

Works for me - see attached.forum.ozgrid.com/index.php?attachment/71301/

Filter based on multiple criteria separated by commas

Participate now!

Share