How to turn Autofilter off with VBA

[Dave]

Using VBA i want to make sure Autofilter is off before I run some other code.
I ran macro recorder and see that the command

Autofilter

toggles the autofilter on and off. The problem is I just want to turn it
off, so i can't use this command if the autofilter is not on or it turns it
on.

Is there some resource online or within help that describes the macro
commands and which qualifiers you can use with them (I tried "Autofilter off"
and "Autofilter False", but it gives me errors).

Can anyone tell me how to use this command to switch autofilter off.

Thanks
Dave

 

[Mike Glen]

Hi Dave,

Try posting on the developer newsgroup. Please see FAQ Item: 24. Project
Newsgroups. FAQs, companion products and other useful Project information
can be seen at this web address: http://project.mvps.org/faqs.htm

Mike Glen
Project MVP

 

[John]

Using VBA i want to make sure Autofilter is off before I run some other code.
I ran macro recorder and see that the command

Autofilter

toggles the autofilter on and off. The problem is I just want to turn it
off, so i can't use this command if the autofilter is not on or it turns it
on.

Is there some resource online or within help that describes the macro
commands and which qualifiers you can use with them (I tried "Autofilter off"
and "Autofilter False", but it gives me errors).

Can anyone tell me how to use this command to switch autofilter off.

Thanks
Dave

Dave,
As near as I can tell there is a glitch in VBA with regard to finding
the status of the Autofilter. As you noted it can be toggled but that's
it. There are a couple of other VBA methods that should get what you
want but they do not appear to work. I have asked my fellow MVPs if
anyone knows what's going on. If someone knows a trick, we'll post it.

Sorry,
John
Project MVP

 

[Dave]

John

Thanks ! Looks like I will have to make sure it is always off in the
meantime.

Dave

 

[John]

John

Thanks ! Looks like I will have to make sure it is always off in the
meantime.

Dave

Dave,
After I was enlightened it turns out it can be done easily with VBA. Use
the following code:

Sub AutoFilOff()
If ActiveProject.AutoFilter = True Then
ActiveProject.AutoFilter = False
End If
End Sub

Sorry for the misdirection. Sometimes VBA can be tricky and it tricked
me this time.

John
Project MVP

 

[EarlePearce]

Or simply:

Sub AutoFltrTgl()
ActiveProject.AutoFilter = Not ActiveProject.AutoFilter
End sub

 

[Rowland Hamilton]

EarlePearc et al:

Can't get any of these codes to work in Excel 2010:

Sub AutoFilOff()
If ActiveProject.AutoFilter = True Then
ActiveProject.AutoFilter = False
End If
End Sub

or your's

Sub AutoFltrTgl()
ActiveProject.AutoFilter = Not ActiveProject.AutoFilter
End sub

Love that they are simple and it doesn't matter where the filter is in the worksheet. Any ideas why it won't work?

Thanks - Rowland