Excel VBA to Filter in Same Column by Multiple Criteria (6 Examples)

In this tutorial, we will demonstrate the use of Excel VBA to filter in the same column by multiple criteria. In Microsoft Excel using VBA to filter a dataset is a fast and convenient way. To illustrate the concept of this article, we will apply filters in the same column based on multiple criteria using the VBA filter.

Excel VBA to Filter in Same Column by Multiple Criteria: 6 Examples

In this article, we will explain 6 examples of using Excel VBA to filter in the same column by multiple criteria. For all the examples in this article, we will use the same dataset given below. The dataset contains Date. name of the City, and Sales for each city on a particular day.

1. Remove Specific Values with VBA to Filter in the Same Column by Multiple Criteria in Excel

First and foremost we will remove specific values from the same column using the Excel VBA filter by multiple criteria. For instance, we will filter the City column of our given dataset. We will remove the values California & Texas from the column City with an Excel VBA filter. Let’s see the steps to perform this action.

STEPS:

• To begin with, right-click on the worksheet tab named REMOVE.
• In addition, select the option ‘View Code’.

• The above action will open a blank VBA code window for that worksheet. Another way to open that code window is to press Alt + F11.
• Furthermore, type the following code in that code window:

Sub Remove()
Application.DisplayAlerts = True
Worksheets("Remove").Activate
Columns("C:C").Select
    Selection.AutoFilter
    ActiveSheet.Range("$C$4:$C$15").AutoFilter Field:=1, _
    Criteria1:="<>California", Criteria2:="<>Texas", _
    Operator:=xlAnd
End Sub

• After that, click on the Run or press the F5 key to run the code.

• Finally, we get results like the image below. We can see that the values California & Texas have been filtered from the column City.

Read More: Filter Multiple Criteria in Excel with VBA (Both AND and OR Types)

2. Keep Particular Values in the Same Column with Excel VBA

Unlike the previous example in this example, we will keep a particular value in the same column instead of removing it. To do this we will apply an Excel VBA filter by multiple criteria in the same column. In the previous example, we have removed the value ‘California’ & ‘Texas’. But, in this example, we will keep these values after applying filters whereas we will remove the other ones. Let’s see the steps to follow for this example.

STEPS:

• First, right-click on the worksheet tab named KEEP.
• Next, click on the option ‘View Code’.

• It opens a blank VBA code window for the active worksheet. We can also press Alt + F11 to get that code window.
• Then, insert the following code in that code window:

Sub Keep()
Application.DisplayAlerts = True
Worksheets("KEEP").Activate
Columns("C:C").Select
Selection.AutoFilter
ActiveSheet.Range("$C$4:$C$15").AutoFilter Field:=1, _
Criteria1:=Array( _
"California", "Texas"), Operator:=xlFilterValues
End Sub

• After that, to run the code click on the Run or press the F5 Key.

• As a result, we can see the result in the below image. Only the values present in the City column are California and Texas. Other values have been filtered.

3. VBA to Filter by Multiple Criteria with Advanced Criteria Range in Same Column

In the third example, we will apply Excel VBA to filter by multiple criteria with advanced criteria ranges in the same column. We will use the dataset of our previous examples. From the image below we can see the advanced criteria also. We are going to filter the data set for two criteria. The first one is that the city will be New York and the sales amount will be greater than $3000. The second one is that the city will be in California and the sales amount will be less than $3000. Just follow the below steps to perform this method.

STEPS:

• Firstly, right-click on the worksheet tab name ‘Criteria_range’.
• Secondly, click on the option ‘View Code’.

• So, we will get a new blank VBA code window for the active worksheet.
• Thirdly, input the following code in that code window:

Sub Advanced_Criteria()
Range("B4:D15").AdvancedFilter Action:=xlFilterInPlace, _
CriteriaRange:=Range("F6:G8")
End Sub

• Then, press the F5 key or click on the Run to run the code.

• Lastly, we get results like the image below. The filtered data in the image below follows our criteria range.

4. Filter in the Same Column Using VBA with OR Criteria in Excel

Another example of applying filters in the same column is to use VBA with OR criteria in Excel.

We will use two criteria in our dataset. The OR criteria will return a value if the values from our dataset meet any of the defined two criteria. To perform this example in our dataset follow the below steps.

STEPS:

• In the beginning, right-click on the active sheet tab named OR.
• Next, select the option ‘View Code’.

• The above command opens a blank VBA code window for that worksheet. We can also press Alt + 11 to open that code window.
• Then, type the following code in the blank code window:

Sub OR_Criteria()
Worksheets("OR").Range("B4").AutoFilter Field:=2, _
Criteria1:="New York", Operator:=xlOr, Criteria2:=">3000"
End Sub

• Now, to run the code click on the Run or press the F5.

• Lastly, we get our dataset filtered for the defined criteria range.

Read More: How to Filter Based on Cell Value Using Excel VBA

5. Insert AND Criteria with Excel VBA to Filter in the Same Column

The AND criteria will return the values which will meet both criteria that we will fix. Using AND criteria we can filter in the same column using Excel VBA by multiple criteria. We will filter the data in our dataset for sales values between $2000 & $3500. Let’s take a look at the steps to apply AND criteria with the VBA filter in our dataset.

STEPS:

• To begin with, right-click on the active sheet named AND.
• In addition, click on the option ‘View Code’.

• So, a new blank VBA code window will open for the active worksheet. An alternative to open that code window is to press Alt + 11.
• Next, copy the following code in that code window:

Sub AND_Criteria()
Worksheets("AND").Range("B4").AutoFilter Field:=3, _
Criteria1:=">2000", Operator:=xlAnd, Criteria2:="<3500"
End Sub

• Furthermore, click on the Run or hit the F5 key to run the code.

• Lastly, we can see the result for filtered data in the image below.

6. Put VBA to Filter by Multiple Criteria in the Same Column with Data Range

In the last example, we will apply VBA to filter with data range. To do this we will fix multiple criteria in the same column. Despite the use of AND criteria, we are showing this example since we will filter the dataset for a date range. Follow the below steps to apply a VBA filter with the data range in our dataset.

STEPS:

• First, go to the active worksheet tab named DateRange and right-click.
• Next, select the option ‘View Code’.

• It opens a new blank VBA code window for that worksheet. We can also hit Alt + 11 to get that code window.
• Then, type the following code in that code window:

Sub Date_Range()
Worksheets("DateRange").Range("B4:D15").AutoFilter Field:=1, _
Criteria1:=">=12-03-21", _
Operator:=xlAnd, _
Criteria2:="<=12-08-21"
End Sub

• Now, to run the code click on the Run or press the F5 key.

• In the end, we get the Date column filtered by our given criteria.

Read More: Excel VBA: How to Filter with Multiple Criteria in Array

Excel VBA to Turn off AutoFilter

In the following image, we can see that the filter is already applied to the dataset. In this section, we will use VBA to turn off the filter applied in this dataset.

So, let’s see the steps to perform this method.

STEPS:

• Firstly, right-click on the active worksheet.
• Secondly, select the option ‘View Code’.
• So, a blank VBA code window will appear for that worksheet.
• Thirdly, copy the following code in that code window:

Sub Turn_Off_Filter()
Worksheets("TurnOff").AutoFilterMode = False
End Sub

• After that, click on the Run or press the F5 key to run the code.

• Lastly, we can see the result in the following image. Our dataset no longer has any filters.

Turn on AutoFilter in Excel Using VBA

In this section, we will use a VBA code to turn on the auto filter for our dataset. Follow the below steps to do this.

STEPS:

• To begin with, select the active worksheet tab named ‘TurnOn’ and right-click on it.
• In addition, select the option ‘View Code’.

• So, it will return a blank VBA code window.
• Furthermore, input the following code in the code window:

Sub Turn_On_Filter()
If Not Worksheets("TurnOn").Range("B4").AutoFilter Then
Worksheets("TurnOn").Range("B4").AutoFilter
End If
End Sub

• Next, press the F5 key or click on the Run to run the code.

• As a result, we can see filter icons in the header cells of our dataset.

Read More: How to Remove Filter in Excel VBA

Check If the Filter Is Applied or Not

Suppose we are working with thousands of cells. It’s not possible to identify if the dataset has filters applied to it only by looking at it. So, in this method, we will use a VBA code to see if the dataset contains a filter or not.

Let’s see the steps which we have to follow in this method.

STEPS:

• In the beginning, right-click on the active worksheet tab.
• Next, select the option ‘View Code’.
• Then, a blank VBA code window will open for that worksheet. We can also open that code window by pressing Alt + F11.
• After that type the following code in that code window:

Sub Filter_Check()
If ActiveSheet.AutoFilterMode = True Then
MsgBox "Active worksheet have filters already in place"
Else
MsgBox "Active worksheet doesn't contain any filter
End If
End Sub

• Now, to run the code click on the Run or press the F5.

• Lastly, we get a message box displaying the message ‘Active worksheet have filters already in place’.

Download Practice Workbook

We can download the practice workbook from here.

Conclusion

In conclusion, this tutorial explains different examples of using the Excel VBA filter with multiple criteria in the same column. So, to put your skills to the test, download the sample worksheet included in this article. Please leave a comment in the box below if you have any questions. Our team will try to reply to your message as quickly as possible. In the future, keep an eye out for more innovative Microsoft Excel solutions.

Related Articles

• Excel VBA: Filter Based on Cell Value on Another Sheet
• Excel VBA: Filter Table Based on Cell Value
• VBA Code to Filter Data in Excel
• Filter Different Column by Multiple Criteria in Excel VBA