Multiple Criteria Filter VBA

jengesmeriz

New Member
Joined
May 9, 2018
Messages
2
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
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,735
Office Version
365
Platform
Windows
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?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,735
Office Version
365
Platform
Windows
@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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,424
Office Version
365
Platform
Windows
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.
Code:
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
 
Last edited:

jengesmeriz

New Member
Joined
May 9, 2018
Messages
2
Hi,

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

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,743
Office Version
2013
Platform
Windows
Try this:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,672
Messages
5,445,896
Members
405,368
Latest member
mistersend

This Week's Hot Topics

Top