# VBA to AutoFilter with Multiple Criteria on Same Field in Excel (4 Methods)

Get FREE Advanced Excel Exercises with Solutions!

Excel’s AutoFilter feature is really efficient in extracting data based on certain conditions. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you 4 methods to AutoFilter with multiple criteria on the same field (column) in Excel with the VBA macro.

## Use Excel VBA to AutoFilter with Multiple Criteria on the Same Field: with 4 Methods

Following this section, you will learn how to AutoFilter with multiple texts and numeric values, with AND operator and OR operator on the same column in Excel with VBA in 4 different criteria.

### 1. Embed VBA to AutoFilter with Multiple Numeric Criteria on the Same Column

Consider the following dataset. Column B consists of some random numbers, whereas Column D holds only the Odd Numbers. What we are going to do here is, we will filter Column B based on the criteria lying in Column D; that means, random numbers (Column B) will be filtered by Odd Numbers with the criteria of including and between the numbers residing in Column D. Let’s see how to do that with VBA in Excel.

Steps:

• In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor. • Next, in the pop-up code window, from the menu bar, click Insert -> Module. • Then, 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`````` • Now, 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. As you can see from the above image, Column B is now filtered with only the odd numbers.

You can perform the same code to filter data based on even numbers. In that case, you just have to store even numbers in another column instead of odd numbers.

VBA Code Explanation

``Dim iArray As Variant``

Define the variable for the array.

``With ThisWorkbook.Worksheets("Column")``

Declare the worksheet name (“Column” is the sheet name for our dataset). You must write the sheet name according to your dataset.

``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)))``

Fill the defined array with the criteria stored in Column D starting on Cell D5.

``````.Range("B4").AutoFilter Field:=1, Criteria1:=iArray, Operator:=xlFilterValues
End With``````
• Filter Column B starts on Cell B4 according to the multiple criteria stored in the defined array.
• After that, leaves the worksheet.

Read More: Excel VBA to Check If AutoFilter is On

### 2. Implement VBA to AutoFilter with AND Operator on Same Column in Excel

The xlAND operator in Excel works with two criteria. It returns the values that fulfill both criteria.

Now, consider the following dataset. Column B consists of random numbers, and we inserted two conditions in the range D4:E5. The conditions are Column B has to be filtered with the numbers that are Greater Than Or Equal to 2 (value stored in Cell E4) and Less Than Or Equal to 9 (value stored in Cell E5). We will filter Column B according to those conditions with the AND Operator in Excel VBA. The steps to get that are given below.

Steps:

• Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, 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`````` • After that, Run the macro as we showed you in the above section. The result is shown in the image below. After successful code execution, Column B is filtered with the values from 2 to 9, that fulfill both conditions.

VBA Code Explanation

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

This piece of code,

• First, declare the worksheet name that we will be working with (“AND” is the sheet name for our dataset). You must write the sheet name according to your dataset.
• After that, filter Column B starts on Cell B4 according to the multiple criteria stored in Cell E4 and E5 with the xlAnd operator.
• Then, leaves the worksheet.

### 3. Apply Macro to AutoFilter with OR Operator on Same Column in Excel

The xlOR operator in Excel works with two criteria. Unlike the xlAND, it returns the values that fulfill any of the criteria.

Now, consider the following dataset. Column B consists of random numbers, and we inserted two conditions in the range D4:E5. The conditions are Column B has to be filtered with the numbers that are Greater Than Or Equal to 12 (value stored in Cell E4) or Less Than Or Equal to 7 (value stored in Cell E5). We will filter Column B according to those conditions with the OR Operator in Excel VBA. Let’s see the steps on how to do that.

Steps:

• As shown before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, 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`````` • Later, Run the macro and look at the following image to see the output. After successful code execution, Column B is filtered with the values that are greater than or equal to 12 or less than or equal to 7.

VBA Code Explanation

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

This piece of code,

• First, declare the worksheet name that we will be working with (“OR” is the sheet name for our dataset). You must write the sheet name according to your dataset.
• After that, filter Column B starts on Cell B4 according to the multiple criteria stored in Cell E4 and E5 with the xlOr operator.
• Then, leaves the worksheet.

### 4. Embed VBA to AutoFilter Based on Multiple Text Values on the Same Field

Look at the following dataset. Column B consists of Country Names. We will filter this column based on the country that we will hardcode in the macro. We will AutoFilter the column based on two country names, Australia and England. The steps to execute this are given below.

Steps:

• Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
• Then, 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`````` • Next, Run the macro. Now, look at the following image to see the result. As a 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.

VBA Code Explanation

``Dim iArray As Variant``

Define the variable for the array.

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

Store the text criteria based on which the filtering will be performed in the defined array.

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

Filter Column B starts on Cell B4 according to the multiple text criteria hardcoded in the defined array.

## Conclusion

To conclude, this article showed you 4 methods to AutoFilter with multiple criteria on the same field (column) in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

## Related Articles Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  