excelvba

Remove an autofilter from a range in VBA


Consider the following VBA that adds an autofilter with two criterion:

Sub FilterRows()
With Worksheets("Sheet1").Range("A1")
.AutoFilter field:=2, Criteria1:="1"
.AutoFilter field:=3, Criteria1:="2"
End With
End Sub

This adds an autofilter to the sheet with two criterion, one on column C and one on D.

How do I turn off the second filter, that is, remove the criterion on field 3 while leaving the first intact?


Solution

  • This worked for me:

    Sub FilterRows()
        With Worksheets("Sheet1").Range("A1").CurrentRegion
            .AutoFilter Field:=2, Criteria1:="1"
            .AutoFilter Field:=3, Criteria1:="2"
        End With
    End Sub
    
    Sub RemoveOneFilter()
        With Worksheets("Sheet1")
            If Not .AutoFilterMode = True Then Exit Sub
            .Range("A1").CurrentRegion.AutoFilter Field:=3
        End With
    End Sub