Reset AutoFilter in code

  • Thread starter Thread starter Brian Bermingham
  • Start date Start date
B

Brian Bermingham

Hi

I know how to remove AutoFilter.
Worksheets("Absence").AutoFilterMode = False

But how can I leave the autofilter in place just removing any applied filter?
Some thing like AutoFilter.reset !

Thanks

Brian
 
Option Explicit
Sub SetAutoFilter()
Dim target As Range
Set target = Range("A1:C1")
FilterA target
'turn filter OFF
target.AutoFilter
'TURN FILTER ON
target.AutoFilter
End Sub
Sub FilterA(target As Range)
' turns filter ON with a filter
target.AutoFilter Field:=1, Criteria1:="=*1", Operator:=xlAnd
End Sub
 
Thanks Patrick
I got that to work.
It seems a bit complex for what apears to be a simple operation.
Is there not a simple command to reset autofilter?

Thanks

Brian
 
it was a demo

you need two lines of code
target.AutoFilter
target.AutoFilter

the first turns off/on and the second reverses it
 
Thanks again Patrick

I now have below which does exactly what I was looking for.

Dim target As Range
Set target = Range("A1:AN1")
If ActiveSheet.AutoFilterMode Then
'turn filter OFF if already on
target.AutoFilter
'then TURN FILTER ON to reset
target.AutoFilter
Else
'turn filter On if already off
target.AutoFilter
End If
 
we appreciate the feedback. many thanks

Brian Bermingham said:
Thanks again Patrick

I now have below which does exactly what I was looking for.

Dim target As Range
Set target = Range("A1:AN1")
If ActiveSheet.AutoFilterMode Then
'turn filter OFF if already on
target.AutoFilter
'then TURN FILTER ON to reset
target.AutoFilter
Else
'turn filter On if already off
target.AutoFilter
End If
Click to expand...
 
Thanks Charlotte

It's always good to know more than one way to do things.

Brian
 
Back
Top