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 SubYour code is now ready to run.
- 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 VariantDefine 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, leave the worksheet.
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 SubYour code is now ready to run.
- 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 SubYour code is now ready to run.
- 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 WithThis 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 SubYour code is now ready to run.
- 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 VariantDefine 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, , 0Filter Column B starts on Cell B4 according to the multiple text criteria hardcoded in the defined array.
Download Practice Workbook
You can download the free practice Excel workbook from here.
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
- 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
