How to AutoFilter with VBA

AutoFilter provides us with a much faster alternative to loops of all kinds.

In the majority of cases it's faster and more efficient to use one of Excel's built in features as apposed to re-inventing the wheel with VBA code. This is why those that have learn Excel from the ground-up know what native features Excel has to offer. While those only familiar with VB/VBA tend to be the ones who re-invent the wheel.

Example 1

1 2 3

Sub Sample1() Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:="A" End Sub

Sample1 Source could simplified with:

1 2 3

Sub Sample2() Range("A1").AutoFilter Field:=2, Criteria1:="A" End Sub

Example 2

1 2 3

Sub Sample3() Range("A1").AutoFilter End Sub

Example3

1 2 3 4 5 6 7 8 9

Sub Sample4() Dim myRange As AutoFilter Set myRange = ActiveSheet.AutoFilter If Not myRange Is Nothing Then MsgBox "Setting" Else MsgBox "Not Setting" End If End Sub

Source in Example3 also can write with:

1 2 3 4 5 6 7 8 9

Sub Sample5() Dim myRange As AutoFilter Set myRange = ActiveSheet.AutoFilter If TypeName(myRange) = "AutoFilter" Then MsgBox "Setting" Else MsgBox "Not Setting" End If End Sub

1 2 3 4 5 6 7

Sub Sample6() If ActiveSheet.AutoFilterMode Then MsgBox "Setting" Else MsgBox "Not Setting" End If End Sub

Example4

1 2 3 4 5 6 7

Sub Sample7() Dim n As Long If ActiveSheet.AutoFilterMode Then n = ActiveSheet.AutoFilter.Filters.Count MsgBox n & " Filters" End If End Sub

Example5

 

1 2 3 4 5 6 7 8 9 10

Sub Sample8() Dim i As Long If ActiveSheet.AutoFilterMode Then For i = 1 To ActiveSheet.AutoFilter.Filters.Count If ActiveSheet.AutoFilter.Filters(i).On Then MsgBox i & " column as filter" End If Next i End If End Sub

Example6

1 2 3 4 5 6 7 8 9 10 11

Sub Sample9() Dim i As Long, Title As String If ActiveSheet.AutoFilterMode Then For i = 1 To ActiveSheet.AutoFilter.Filters.Count If ActiveSheet.AutoFilter.Filters(i).On Then Title = ActiveSheet.AutoFilter.Range.Cells(1, i) MsgBox Title & " as the filter" End If Next i End If End Sub

Download File