Detect autofilter status

[Rob T]

I have a spreadsheet which has autofilter on. I have a macro which selects certains areas (using autofilter) and copies them to another sheet.

The trouble arises if some filters are left on but users, then obviously it just adds more filters and copies what's left across. I have a simple loop which goes through and sets all the filters to show all. The trouble is that this loop takes forever to run and that particular loop runs twice. With that loop in place the macro takes just over 5 minutes to run, if I comment it out the rest of the code takes 15 seconds to run.

Is there any way to detect what the autofilter status is for a column and then only change its state if it's set to filter?

Any help would be appreciated.

Cheers,

Rob

[VBA Noob]

Maybe you could turn it off before you run the macro

Please Login or Register  to view this content.

VBA Noob

[Rob T]

Thank-you. I don't know why I didn't think of that

That cut the run-time down from 5 minutes to 22 seconds!!!

Cheers,

Rob

[Richard Schollar]

Hi Rob

You can check for applied filters without removing autofilter altogether:

Please Login or Register  to view this content.

which will check first if a filter has been applied and then remove it if it has (removes all filters, across all columns).

Since filtering triggers recalculation, you may find your macro's speed can be improved by using

Please Login or Register  to view this content.

Hope this helps!

Richard