32. VBA Tips - Turn Off Autofilter

Nick's picture

Turn Off Autofilter

Turning off autofilter using VBA is easy and quick, but you need to know how it's done.

Here's some code to toggle the autofilter on and off:

Sub ToggleAutofilter()
    ' check if autofilter is currently off
    If ActiveSheet.FilterMode = False Then
        ' if it's off, turn it on, and filter on "Nick"
        Range("d5").AutoFilter Field:=1, Criteria1:="Nick"
        ' if it's on, turn it off
    End If
End Sub

Download sheet to practise how to Turn Off Autofilter in Excel


  Training video on how to Turn Off Autofilter in Excel:

turn-off-autofilter.xls56 KB
Vishesh's picture

Autofilter (Advanced)

Just a quick piece of info...
With advanced filter in Excel while trying to find out Unique, the limitation is that the destination range should be on the same sheet as the source. However, using VBA there is no such limitation. Using VBA the source and destination ranges in Advanced filter need not be on the same sheet.