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

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

STEPS:

• Right-click on the worksheet tab named REMOVE.
• Select the option ‘View Code’.

• The above action will open a blank VBA code window for that worksheet or to press Alt + F11.
• 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

• Click on the Run or press the F5 key to run the code.

• Get results like the image below. The values California & Texas have been filtered from the column City.

Method 2 – Keep Particular Values in the Same Column with Excel VBA

STEPS:

• Right-click on the worksheet tab named KEEP.
• Click on the option ‘View Code’.

• It opens a blank VBA code window for the active worksheet or press Alt + F11 to get that code window.
• 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

• Run the code, click on the Run, or press the F5 Key.

• See the result in the below image. Only the values present in the City column are California and Texas. Other values have been filtered.

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

STEPS:

• Right-click on the worksheet tab name ‘Criteria_range’.
• Click the option ‘View Code’.

• Get a new blank VBA code window for the active worksheet.
• Input the following code in that code window:

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

• Press the F5 key or click on the Run to run the code.

• Get results like the image below. The filtered data in the image below follows our criteria range.

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

STEPS:

• Right-click on the active sheet tab named OR.
• Select the option ‘View Code’.

• The above command opens a blank VBA code window for that worksheet or press Alt + 11 to open that code window.
• 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

• Run the code, click on the Run, or press the F5.

• Get our dataset filtered for the defined criteria range.

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

STEPS:

• Right-click on the active sheet named AND.
• Click on the option ‘View Code’.

• A new blank VBA code window will open for the active worksheet or press Alt + 11.
• 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

• Click Run or hit the F5 key to run the code.

• See the result for filtered data in the image below.

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

STEPS:

• Go to the active worksheet tab named DateRange and right-click.
• Select the option ‘View Code’.

• It opens a new blank VBA code window for that worksheet or hit Alt + 11 to get that code window.
• 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

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

• Get the Date column filtered by our given criteria.

Excel VBA to Turn off AutoFilter

STEPS:

• Right-click on the active worksheet.
• Select the option ‘View Code’.
• A blank VBA code window will appear for that worksheet.
• Copy the following code in that code window:

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

• Click Run or press the F5 key to run the code.

• See the result in the following image. Our dataset no longer has any filters.

Turn on AutoFilter in Excel Using VBA

STEPS:

• Select the active worksheet tab named ‘TurnOn’ and right-click on it.
• Select the option ‘View Code’.

• It will return a blank VBA code window.
• 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

• Press the F5 key or click on the Run to run the code.

• See filter icons in the header cells of our dataset.

Check If the Filter Is Applied or Not

STEPS:

• Right-click on the active worksheet tab.
• Select the option ‘View Code’.
• A blank VBA code window will open for that worksheet or press Alt + F11.
• 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

• Run the code, click Run, or press the F5.

• A message box displaying the message ‘Active worksheet have filters already in place’.

Download Practice Workbook

We can download the practice workbook from here.

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