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

Excel’s AutoFilter feature is really efficient to extract 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.


Download Workbook

You can download the free practice Excel workbook from here.


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

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

Your code is now ready to run.

VBA to AutoFilter with Multiple Numeric Criteria on Same Field in Excel

  • Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small 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.

Result of VBA to AutoFilter with Multiple Numeric Criteria on Same Field in Excel

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 (4 Easy Ways)


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

xlAND operator in Excel works with two criteria. It returns the values that fulfil 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

Your code is now ready to run.

VBA to AutoFilter with Multiple Criteria with AND on Same Field in Excel

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

Result of VBA to AutoFilter with Multiple Criteria with AND on Same Field in Excel

After successful code execution, Column B is filtered with the values from 2 to 9, that fulfil 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 declares 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.

Read More: [Fix]: AutoFilter Method of Range Class Failed (5 Solutions)


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

xlOR operator in Excel works with two criteria. Unlike xlAND, it returns the values that fulfil 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

Your code is now ready to run.

VBA to AutoFilter with Multiple Criteria with OR on Same Field in Excel

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

Result of VBA to AutoFilter with Multiple Criteria with OR on Same Field in Excel

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 declares 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.

Read More: How to Autofilter and Copy Visible Rows with Excel VBA


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 that 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

Your code is now ready to run.

VBA to AutoFilter with Multiple Text Criteria on Same Field in Excel

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

Result of VBA to AutoFilter with Multiple Text Criteria on Same Field in Excel

As a result, Column B which was filled with so many countries are 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.

Read More: How to Autofilter Values Not Equal to a Certain Value with VBA in Excel


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

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

Leave a reply

ExcelDemy
Logo