AutoFilter toggling in VBA (How to use correctly)

[Dennis]

XL 2003

In VBA I calculate a range (myRange) to apply in an autofilter.

I have used both of the following code and the AutoFilter toggles on to off
each time I run the code. My goal is to have the VBA code remove all
previous settings then apply
AutoFilter to myRange. Therefore I tried to clear settings:

Selection.AutoFilter -or- ActiveSheet.UsedRange.AutoFilter

Later in the same procedure I used:

If myRange.AutoFilter Then myRange.AutoFilter

-or-

myRange.AutoFilter

No matter what my approach, the AutoFilter toggles on to off
each time I run the procedure.

Where is my logic breaking down?

Dennis

[Norman Jones]

Hi Dennis,

Try something like:

Sub Tester()
With ActiveSheet
If .AutoFilterMode Then
If .FilterMode Then .ShowAllData
Else
.Range("A1").AutoFilter
End If
End With
End Sub

---
Regards,
Norman



"Dennis" <[email protected]> wrote in message
news:[email protected]...
> XL 2003
>
> In VBA I calculate a range (myRange) to apply in an autofilter.
>
> I have used both of the following code and the AutoFilter toggles on to
> off
> each time I run the code. My goal is to have the VBA code remove all
> previous settings then apply
> AutoFilter to myRange. Therefore I tried to clear settings:
>
> Selection.AutoFilter -or- ActiveSheet.UsedRange.AutoFilter
>
> Later in the same procedure I used:
>
> If myRange.AutoFilter Then myRange.AutoFilter
>
> -or-
>
> myRange.AutoFilter
>
> No matter what my approach, the AutoFilter toggles on to off
> each time I run the procedure.
>
> Where is my logic breaking down?
>
> Dennis