VBA to AutoFilter with Multiple Criteria on Same Field in Excel: 4 Methods

Method 1 – Embed VBA to AutoFilter with Multiple Numeric Criteria on the Same Column

Steps:

• Press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

• In the pop-up code window, from the menu bar, click Insert -> Module.

• Copy the following code and paste it into the code window.

Sub AutoFilterWithMultipleCriteriaOnSameColumn()
Dim iArray As Variant
With ThisWorkbook.Worksheets("Column")
    iArray = Split(Join(Application.Transpose(.Range(.Cells(5, 4), .Cells(.Range("D:D").Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row, 4)).Value)))
    .Range("B4").AutoFilter Field:=1, Criteria1:=iArray, Operator:=xlFilterValues
End With
End Sub

Your code is now ready to run.

• Press F5 on your keyboard, or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the Run icon in the sub-menu bar to run the macro.

After successful code execution, look at the following image to check out the result.

Column B is now filtered with only the odd numbers.

Perform the same code to filter data based on even numbers. You have to store even numbers in another column instead of odd numbers.

Dim iArray As Variant

With ThisWorkbook.Worksheets("Column")

iArray = Split(Join(Application.Transpose(.Range(.Cells(5, 4), .Cells(.Range("D:D").Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row, 4)).Value)))

.Range("B4").AutoFilter Field:=1, Criteria1:=iArray, Operator:=xlFilterValues
End With

Method 2 – Implement VBA to AutoFilter with AND Operator on Same Column in Excel

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Copy the following code and paste it into the code window.

Sub AutoFilterOnSameColumnWithAND()
With ThisWorkbook.Worksheets("AND")
    .Range("B4").AutoFilter Field:=1, Criteria1:=">=" & .Range("E4").Value, Operator:=xlAnd, Criteria2:="<=" & .Range("E5").Value
End With
End Sub

The code is now ready to run.

• Run the macro as we showed you in the above section. The result is shown in the image below.

Column B is filtered with the values from 2 to 9, that fulfill both conditions.

With ThisWorkbook.Worksheets("AND")
    .Range("B4").AutoFilter Field:=1, Criteria1:=">=" & .Range("E4").Value, Operator:=xlAnd, Criteria2:="<=" & .Range("E5").Value
End With

Method 3 – Apply Macro to AutoFilter with OR Operator on Same Column in Excel

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Copy the following code and paste it into the code window.

Sub AutoFilterOnSameColumnWithOR()
With ThisWorkbook.Worksheets("OR")
    .Range("B4").AutoFilter Field:=1, Criteria1:="<" & .Range("E5").Value, Operator:=xlOr, Criteria2:=">" & .Range("E4").Value
End With
End Sub

Your code is now ready to run.

• Run the macro and look at the following image to see the output.

Column B is filtered with the values that are greater than or equal to 12 or less than or equal to 7.

With ThisWorkbook.Worksheets("OR")
    .Range("B4").AutoFilter Field:=1, Criteria1:="<" & .Range("E5").Value, Operator:=xlOr, Criteria2:=">" & .Range("E4").Value
End With

Method 4 – Embed VBA to AutoFilter Based on Multiple Text Values on the Same Field

Steps:

• Open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Copy the following code and paste it into the code window.

Sub AutoFilterOnSameColumnWithMultipleTexts()
Dim iArray As Variant
iArray = Array("Australia", "England")
Range("B4", Range("B" & Rows.Count).End(xlUp)).AutoFilter 1, iArray, xlFilterValues, , 0
End Sub

Your code is now ready to run.

• Run the macro. Now, look at the following image to see the result.

Column B which was filled with so many countries is now filtered only with two country names – Australia and England – that we provided in the code.

Dim iArray As Variant

iArray = Array("Australia", "England")

Range("B4", Range("B" & Rows.Count).End(xlUp)).AutoFilter 1, iArray, xlFilterValues, , 0

Download Practice Workbook

You can download the free practice Excel workbook from here.

Related Articles

• VBA Autofilter: Sort Smallest to Largest
• Excel VBA to Check If AutoFilter is On
• Excel VBA: Remove AutoFilter If It Exists
• How to Autofilter Values Not Equal to a Certain Value with VBA in Excel