Toggle filters on & off

[kaseyleigh]

Hi

Using a button, is there a way to toggle the filters on & off, for the following code ...

Much appreciated

Please Login or Register  to view this content.

[fredlo2010]

Can you provide a copy of the workbook ?

Thanks

[kaseyleigh]

Hi fredlo2010

It's huge, has 25+ tabs. As you can see by the code above!

Basically, have a filter that on each tab mostly in Col D which filters to show "Blank" rows. The options in the Filter are "Zero" or Blank.
So, aim of filter is to remove rows where all values = Zero... hence choosing Blank in the code.

Want a button when pressed will show Blank and then prsess again will clear the filter

HTH

[kaseyleigh]

OR ...

Is there a better way to write the code I have here?

Thx

[HaHoBe]

Hi, kaseyleigh,

maybe like this:

Please Login or Register  to view this content.

Ciao,
Holger

[kaseyleigh]

Cool! Thank you! Holger

Will need to move the filter to Col D on all sheets so yr code will work

I will now go and learn what each line in your code means and does

Thank you once again!!

[HaHoBe]

Hi, kaseyleigh,

sorry I missed that part. It could be handled by a second Array:

Please Login or Register  to view this content.

Ciao,
Holger

[fredlo2010]

Good job

[kaseyleigh]

Hey Holger!

Thank you so much!

I am a novice at writing code so love learning

Thank you once again!! Have a lovely day in Hamburg

[kaseyleigh]

Morning Holger,

May I ask for your assistance once again?

I'd like to add to the code, when saving the file to PDF ALL wSheets must have the filter enabled to .AutoFilter Field:=1, Criteria1:="="

PROBLEM : If a User cleared the filter on one wSheet and enabled on another, I am going to have some inconsistency. I thought about hiding the filter columns (easy solution) but then your code wouldn't work

I'll be very grateful for your help

Thanks

[HaHoBe]

Hi, kaseyleigh,

this code takes the AufoIlterMode from the first worksheet of the array and toggles the state. If you want to make sure that the filters are turned on you should assign True to the variable blnFilterOn.

Please Login or Register  to view this content.

The case of turning the Filter into a wanted mode could be asserted by passing a variable as a pamaeter or using a global variabl or by using a modified code for the PDF.

Ciao,
Holger

[kaseyleigh]

Wow! I'm afraid you lost me in the jargon a relative newbie to VBA.

If I wanted to Hide all the filter columns in the wSheets, how would your code Sub FilterOutZeros_2() cope with that?

Many thanks

[HaHoBe]

Hi, kaseyleigh,

you would need to rewrite the code asit checks for teh individual setting of Autofilter whereas 3 takes the code from the first sheet and toggles that throughout. Maybe you should consider an extra worksheet where you may have the names of the worksheets, the columns to filter and the status (you wouldnīt need the arrays) and work that over in one look and convert the status being stated there.

Ciao,
Holger

[kaseyleigh]

OK, I think that will be better to add another ws.

How do I add a status of the filter? and how would it change if filter was On or Off? Is there a function I am unaware of?

Thank you!

[HaHoBe]

Hi, kaseyleigh,

inside the code UI supplied itīs checked to see if a sheet features the filter which could be used to do as you request. This would return either TRUE if used or FLASE if not. That could be simply reverted by

Please Login or Register  to view this content.

from TRUE to FLASE and reverse. You may use a cell for each sheet individually or one for the total of the sheets.

Ciao,
Holger

[kaseyleigh]

Hey thanks!

I managed to come up with a solution (miracles) see below ..

Please Login or Register  to view this content.

Thanks for all your advice!

[kaseyleigh]

Any ideas on how to deactivate the Save button?
OR
should I open a new thread?

Much appreciated,
K