Reset AutoFilter in code

[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

 

[Patrick Molloy]

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

 

[Brian Bermingham]

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

 

[Patrick Molloy]

it was a demo

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

the first turns off/on and the second reverses it

 

[Brian Bermingham]

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

 

[Patrick Molloy]

we appreciate the feedback. many thanks

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

 

[Charlotte E]

But how can I leave the autofilter in place just removing any applied

filter? Some thing like AutoFilter.reset !

One simple quick line

ActiveSheet.ShowAllData

That's it

 

[Brian Bermingham]

Thanks Charlotte

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

Brian