Filter - VBA Multiple "Contains"

[jamesmev]

I am attempting to add a VB filter that is filtering based on the range containing a select number of options.

Example:
Column E -
ActiveSheet.Range("E:E").AutoFilter Field:=5, Criteria1:= _
Array("*" & "HLT_", "CCS_", "LCB_", "ACB_", & "*"), Operator:=xlFilterValues

If the data in E contains any of those criteria it will filter to the remaining out.

Dog
HLT_Dog
HLT_Cat
HTL_Mouse
Mouse
LCB_Mouse

I would only want the results with the prefix.

Any help would be appreciated.

 

[steve the fish]

You cant do that. If you attempted to do that manually you would be restricted to two values and the same is true in code. There will be workarounds but depends what you are attempting to do.

 

[kevin9999]

As @steve the fish pointed out, there are workarounds. Here's a demonstration of one way. Based on your values being in column E.

Option Explicit
Sub WildCardFilter()
    Dim i As Long, ws As Worksheet, lr As Long, c As Range, arr() As Variant
    Set ws = ActiveSheet
    lr = ws.Cells(Rows.Count, 5).End(xlUp).Row
    For Each c In ws.Range("E2:E" & lr)
        If c.Value Like "HLT_*" Or _
        c.Value Like "CCS_*" Or _
        c.Value Like "LCB_*" Or _
        c.Value Like "ACB_*" Then
            ReDim Preserve arr(i)
            arr(i) = c.Value
            i = i + 1
        End If
    Next c
    With ws.Range("A1").CurrentRegion
        .AutoFilter 5, Array(arr), 7
    End With
End Sub

 

[jamesmev]

Thank you this is a perfect work around!
Much Appreciated!

 

[kevin9999]

Thank you this is a perfect work around!
Much Appreciated!

Glad we could help, and thanks for the feedback

 

[tapervaz]

As @steve the fish pointed out, there are workarounds. Here's a demonstration of one way. Based on your values being in column E.

Option Explicit
Sub WildCardFilter()
    Dim i As Long, ws As Worksheet, lr As Long, c As Range, arr() As Variant
    Set ws = ActiveSheet
    lr = ws.Cells(Rows.Count, 5).End(xlUp).Row
    For Each c In ws.Range("E2:E" & lr)
        If c.Value Like "HLT_*" Or _
        c.Value Like "CCS_*" Or _
        c.Value Like "LCB_*" Or _
        c.Value Like "ACB_*" Then
            ReDim Preserve arr(i)
            arr(i) = c.Value
            i = i + 1
        End If
    Next c
    With ws.Range("A1").CurrentRegion
        .AutoFilter 5, Array(arr), 7
    End With
End Sub

I had a similar work case and this works perfectly. Registered here to Thank You!

 

[kevin9999]

I had a similar work case and this works perfectly. Registered here to Thank You!

You're welcome and thanks for the feedback ?