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 values in array. Can anyone help me on this? I'm 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. Below is my sample code.
Sub Categorized()
Dim lastRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Test")
'find the last row
lastRow = ws.Range("I" & Rows.Count).End(xlUp).Row
'turn off autofilter if on
If ws.AutoFilterMode = True Then
ws.AutoFilterMode = False
End If
ApplicationScreenUpdating = False
With ws
'filter the column for "EXTRA AIRCON"
.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
This has been cross-posted to mrexcel.com/forum/excel-questions/1064023-multiple-criteria-filter-vba.html"
Bookmarks