Removing Autofilter Without Resetting All Filters

[AJLS]

Hi All,

I'm hoping this is a straightforward one, I can see anything in the older threads. I have four drop down boxed that will filter and sort the data in the worksheet, on the filters i have "All" as the default. The trouble I am having is that when All is selected it resets the all the filters.

I am looking for a command that will remove the filter when All is selected so that any other active filters will still be applied, my code is below:

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$B$1" Then
 If Range("B1") = "All" Then
  Range("A7").AutoFilter
  
 Else
  Range("A7").AutoFilter Field:=6, Criteria1:=Range("B1")
  
 End If
End If

If Target.Address = "$B$2" Then
 If Range("B2") = "All" Then
  Range("A7").AutoFilter
 Else
  Range("A7").AutoFilter Field:=10, Criteria1:=Range("B2")
 End If
End If

If Target.Address = "$B$3" Then
 If Range("B3") = "All" Then
  Range("A7").AutoFilter
 Else
  Range("A7").AutoFilter Field:=4, Criteria1:=Range("B3")
 End If
End If

If Range("B4") = "Drawing Priority" Then

Range("A7:U100").Sort Key1:=Range("L7"), Order1:=xlAscending, Header:=xlNo


End If

If Range("B4") = "Tender Priority" Then

Range("A7:U100").Sort Key1:=Range("N7"), Order1:=xlAscending, Header:=xlNo


End If

If Range("B4") = "SIR Priority" Then

Range("A7:U100").Sort Key1:=Range("M7"), Order1:=xlAscending, Header:=xlNo

End If

If Range("B4") = "Quote No." Then

Range("A7:U100").Sort Key1:=Range("A7"), Order1:=xlAscending, Header:=xlNo

End If

If Range("B4") = "Date Arrived" Then

Range("A7:U100").Sort Key1:=Range("E7"), Order1:=xlAscending, Header:=xlNo

End If
If Range("B4") = "Customer" Then

Range("A7:U100").Sort Key1:=Range("B7"), Order1:=xlAscending, Header:=xlNo

End If

Range("A7:FU100").Rows.AutoFit

End Sub

Thanks in advance

 

[RoryA]

Instead of using:

Range("A7").AutoFilter

on its own, include the field you want to clear the filter on - e.g.:

Range("A7").AutoFilter Field:=7

 

[AJLS]

Instead of using:

Range("A7").AutoFilter

on its own, include the field you want to clear the filter on - e.g.:

Range("A7").AutoFilter Field:=7

I've been trying some ridiculous things and it was this simple lol.

Thanks Rory

 

[RoryA]

Glad we could help.