Keeping the filtering criteria

kyrgyzstanart · #1 02-08-2010, 05:26 AM

Hi,

I have created a protected sheet, when any of my macros run I remove the autofliter, because it can impact my code and the results - it's a necessity.

However, it's super engraging actually, because users use the filters extensively and every time they run a macro all their filter criteria is removed and they have to put it back - super cumbursome.

Therefore, I would like:

1. Before removing the filter I would like to test the filter criteria.
2. Keep that filter criteria.
3. Reapply the filter w/ the last criteria.

Does anyone know how to do that?

Thanks!!!

Palmetto · #2 02-08-2010, 07:37 AM

See this link: Protect Sheets & Enable/Allow Autofilter

kyrgyzstanart · #3 02-08-2010, 10:07 AM

Hello Palmetto,

This doesn't help. I know how to enable filters and disable in protected sheets. What I do not know is to how to keep the filter criteria that were there before I remove the autofilter, and reapply the same criteria after I put a new filter. All of my criteria are arrays...

kyrgyzstanart · #4 02-08-2010, 08:45 PM

No idea? Anyone?

Palmetto · #5 02-08-2010, 09:08 PM

One approach is to create a Custom View.

Filter the data to the state you want to return to, then go to View > Custom View > Add and give the view a meaningful name.

After running other filters, turn off filtering, etc., to return the original state go to View > Custom View, click on the name of the view and click Show.

Record a macro when showing the Custom View and put this in the appropriate place in your code.

Code:

    ActiveWorkbook.CustomViews("MyView").Show

kyrgyzstanart · #6 02-13-2010, 02:32 AM

Custom View doesn't quite work - saying "some custom view can't be shown" or something like this and doesn't put back the filter... still no solution...

Palmetto · #7 02-13-2010, 07:13 AM

Will the filter criteria you want to save always be the same?
If so, store the values in cells and reapply the auto-filter in VBA and reference the cells holding the saved values.

If your filter criteria is always different then possibly . . .

Use Advanced Filter to create a list of unique values for each column you want to filter, using the option to copy the results to a new location
In conjunction with the above, use the SpecialCells(xlCellTypeVisible) property to act only on the currently filtered cells
Create code to apply Advanced Filter (in place)
After running your other code, apply the Advanced Filter code, though you won't have the drop down arrows that Auto-Filter provides.

One last option - not the most efficient
Use Advanced Filter to create a unique list of values copied to new location
Loop through the rows/column(s) to hide rows not matching those filtered values

kyrgyzstanart · #8 02-14-2010, 01:45 AM

Hi Palmetto,

Thanks for your hints. The solution I was thinking about (but never could get it working) is the following:

1. Test if Autofilter is ON.
2. Test the row on which the Autofilter is on to see if any of the columns have any Filtering keys on (the row for filters is always the same).
3. If I find a column on which there are criterias for filtering, I take this column values and put them into another sheet and do "unique values" only in that other sheet in order to get the values on which the filtering was done.
4. When I want to put back the filter I would like to re-use the values from the temporary sheet column to put them into an array... - and this is where I start having troubles. I do NOT know how to assign the values in the array to the filter...

Can you, please, help?

wotadude · #9 02-15-2010, 02:19 PM

you may find this article helpful.
It worked a treat for me when wanting capture and restore autofilter selections
http://www.mrexcel.com/forum/showthread.php?t=333961

kyrgyzstanart · #10 02-17-2010, 04:30 PM

wotadude,

Thanks, mate! This seems to be doing the job! I will test it and late you know.

Welcome to the Excel Forum

Forum Statistics