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.
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.
'Static values held in Excel VBA code will not show filter drop down arrows.
Dim ar As Variant
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
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.
Dim i As Integer
Dim ar(1 To 100) As String 'assumes the list is 100 items.
[A1].AutoFilter 1, ar, xlFilterValues
Range("A1").AutoFilter 'Turn autofilter Off
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.
Dim ar as Variant
ar=Split(Join(ar, ","), ",")
Sheet1.[A1:B10].AutoFilter 1, ar, xlFilterValues
' Sheet1.[a1].AutoFilter 'Turn autofilter Off
The following Excel file outlines the above VBA examples using the autofilter.