Autofilter with Multiple Conditons VBA

The autofilter in Excel with VBA can be used with multiple conditions in a single column.  This can be really useful if you need to isolate data on multiple conditions.  The following methods will autofilter data in place, so the information can be manipulated in Excel after the autofilter is applied.


The following YouTube video takes you through one of the processes of filtering on multiple criteria. File contained below.

Filter Multi Criteria.xlsm

 
 

Filter on Multiple Conditions with Static Array

Being able to autofilter on more than 2 criteria is important lesson to learn. It further avoids iterations and runs really quickly in comparison to looping. The disadvantage is the criteria are hard coded using VBA.

Sub FilterMult1()
'Static values held in Excel VBA code will not show filter drop down arrows.
Dim ar As Variant
ar=Array("Dept A", "Dept B", "Dept C")
Range("A1", Range("A" & Rows.Count).End(xlUp)).AutoFilter 1, ar, xlFilterValues, , 0
Range("A1").AutoFilter 'Turn Filter Off
'Range("A1").AutoFilter 'Turn autofilter Off
End Sub

Filter on Multiple Conditions with Dynamic Array

The following is the same as the above but it uses an Excel range as the criteria. It is more dynamic than the above as it allows your list to grow.

Sub FilterMulti2() 'Use Excel VBA to store Dynamic values in cells
Dim i As Integer
Dim ar(1 To 100) As String 'assumes the list is 100 items.

For i=2 To Sheet2.[A65536].End(xlUp).Row
ar(i - 1)=Sheet2.Range("A" & i)
Next i

[A1].AutoFilter 1, ar, xlFilterValues
Range("A1").AutoFilter 'Turn autofilter Off
End Sub
Filter on Multiple Conditions with Dynamic Array and WIthout a Loop

The following is the same procedure without the need for a loop. It takes the procedure one step further and deletes data that meets criteria.

Sub FilterMulti() 'Excel VBA using Dynamic values stored in Cells (no loop required)
Dim ar as Variant

ar=Sheet2.Range("a2", Sheet2.Range("a" & Sheet2.Rows.Count).End(xlUp))
ar=Application.Transpose(ar)
ar=Split(Join(ar, ","), ",")

Sheet1.[A1:B10].AutoFilter 1, ar, xlFilterValues
Sheet1.[A2:A10].EntireRow.Delete
' Sheet1.[a1].AutoFilter 'Turn autofilter Off
End Sub

The following Excel file outlines the above VBA examples using the autofilter.