Multiple Criteria Filter VBA

[jengesmeriz]

Hi,

I'm trying to create a macro that will filter multiple values using array. However, I'm not sure what is the problem on the code and it wont filter more than two criteria. Im trying to filter column I if it contains "*Extra Air*", "*Extra-Air*", "*ExtraAir*", and if it does then I will tag it as Extra Air Con. The formula works for two criteria, but if I added another one, it wont work. Can anyone help me on this? Below is my sample code.

Sub categorized()
Dim lastRow as long
Dim ws as worksheet
Set ws = Worksheet("Test")
lastRow = ws.Range("I" & Rows.Count).End(xlUp).Row

With ws
.Range("I:I").Autofilter Field:=1, Criteria1:=Array("*Extra Air*", "*Extra-Air*", "*ExtraAir*"), Operator:=xlFilterValues
On error resume next
If lastRow >2 Then
.Range(.Range("C3"),.Range("C" & lastRow)). _
SpecialCells(xlCellTypeVisible).Value = "Extra Air Con"
End if
.AutofilterMode = False
End Sub

 

[Fluff]

Hi & welcome to MrExcel
You can only filter on more than 2 criteria if it's an exact match.
Can you use replace to change one of them before filtering?

 

[vcoolio]

Hi Fluff,

This has been cross-posted here:-

https://www.excelforum.com/excel-pr...ro-to-filter-multiple-values-in-a-column.html

Cheerio,
vcoolio.

 

[Fluff]

@jengesmeriz
Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Peter_SSs]

Welcome to the MrExcel board!

Not quite certain about what rows your data is actually in, but try this in a copy of your workbook.

Sub CheckMultipleValues()
  Dim ws As Worksheet
  Dim RX As Object
  Dim a As Variant
  Dim lr As Long, i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.IgnoreCase = True
  RX.Pattern = "Extra Air|Extra-Air|ExtraAir"
  Set ws = Sheets("Test")
  lr = ws.Cells(ws.Rows.Count, "I").End(xlUp).Row
  a = Application.Index(ws.Cells, Evaluate("row(2:" & lr & ")"), Array(3, 9))
  For i = 1 To UBound(a)
    If RX.test(a(i, 2)) Then a(i, 1) = "Extra Air Con"
  Next i
  ws.Range("C2").Resize(UBound(a)).Value = Application.Index(a, 0, 1)
End Sub

 

[jengesmeriz]

Hi,

Sorry, I'm relatively new to the group and to VBA. But surely this s noted. Thankyou!

 

[My Aswer Is This]

Try this:

Sub Filter_Me_Using_Array_With_Wildcard()
'Modified  7/23/2018  10:04:13 PM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Dim c As Long
Sheets("Test").Activate
Dim Counter As Long
c = 9 ' Column Number Modify this to your need
Lastrow = Cells(Rows.Count, c).End(xlUp).Row
Del = Array("Extra Air", "Extra-Air", "ExtraAir")
ans = UBound(Del)
    For i = 1 To ans + 1
    
        With ActiveSheet.Cells(1, c).Resize(Lastrow)
            .AutoFilter 1, "*" & Del(i - 1) & "*"
            Counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
            If Counter > 1 Then
                .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Value = "Extra Air Con"
            Else
                MsgBox "No values found"
            End If
                .AutoFilter
        End With
    Next
Application.ScreenUpdating = False
End Sub