Home » Blogs » Nick's blog

32. VBA Tips - Turn Off Autofilter

Nick's picture
Submitted by Nick on 4 January, 2010 - 14:43

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"
    Else
        ' if it's on, turn it off
        Range("d5").AutoFilter
    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:

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

Autofilter (Advanced)

Submitted by Vishesh on 9 January, 2010 - 17:42.

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.

»