• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Clear existing AutoFilters - a best practice, but how best to do it?

H

Hartke

New Member
Hello all!


The other day I noticed that some code I'd written was misbehaving. On one sheet it would select the proper column and all would be well, but on another it chose the column next to it, with calamitous results. Here's the code example below:


' .Sheets("Data").Range("$A$9:$V$50000").AutoFilter Field:=5, Criteria1:=myCriteria, Operator:=xlFilterValues '


After a bit of research I found that in some cases the stuff in "Data" already had filters applied. When the filter began in Column A all was well, but sometimes it was in Column B, which caused a mess.


Simple enough solution, but I figured it would be a best practice to always include code that clears out any existing filtering. The question is, how best to do it?


I toyed with using "If ActiveSheet.AutoFilterMode = True Then ActiveSheet.AutoFilterMode = False" but if I'm not mistaken that just turns off my ability to use Filtering, which is not what I want. I also don't want to touch any data, just remove the sorting.


Suggestions?
 
Hi, Hartke!


You can check if autofilter feature is enabled for a worksheet checking this property for true or false:

ActiveSheet.AutoFilterMode

You can clear all filters applied with this:

ActiveSheet.Cells.AutoFilter


So this block should do what you need:

-----

If ActiveSheet.AutoFilterMode Then ActiveSheet.Cells.AutoFilter

ActiveSheet.Cells.AutoFilter

-----


Just advise if any issue.


Regards!
 
Thank you for the speedy reply! Unfortunately, it does not do what I need.


Perhaps because both parts of the If/then statement are the same?


If ActiveSheet.AutoFilterMode Then ActiveSheet.Cells.AutoFilter

ActiveSheet.Cells.AutoFilter
 
Hi, Hartke!


I created a macro with those two lines.

Then I created a new worksheet without the AutoFilter set, run the macro, and it got with AutoFilter enabled and unfiltered.

Then I remove the AutoFilter and run again the macro, same result.

Then I filter data by many columns and run it again, same result.


Could you please try to do so? If it's not your selected sheet, either add a "Worksheets("Data").Activate" first or replace "ActiveSheet" by "Worksheets("Data")" in the little macro.


Regards!
 
I think it should be

[pre]
Code: 
Range("$A$9").Select
If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData
[/pre]
 
Hi, Hartke!


Regarding the IF statement, the condition of AutoFilter enabled (AutoFilterMode = true, not meaning it's applied) is tested to lets the .AutoFilter clear all filters and disable filtering.

Then next .AutoFilter enables it again.


I didn't notice your range started at row 9, then code should be:

-----

If ActiveSheet.AutoFilterMode Then ActiveSheet.Cells.AutoFilter

ActiveSheet.Rows(9).Cells.AutoFilter

-----


Regards!


@Hui

Hi!

Thanks for the catch, I misread :(

BTW, I think ShowAllData method works only if worksheet is previously filtered.

Regards!


EDITED at 22:42 GMT-3
 
@SirJB7

Your correct about the ShowAllData, but seeing it only gets used

If ActiveSheet.AutoFilterMode is True

Then it will be ok


I have responded with quite lengthy responses a few times to re-read the original question and then just delete my response as I was way off

I've learnt the hard way to re-read questions
 
@Hui

Hi!

I actually built 2 two-lines macros, one with my code and the other with your code.

I run the three steps with both:

a) on a clean filter disabled sheet

b) enabling filters but not filtering

c) enabling filters and filtering

And the second macro run only ok in c)

Regards!

PS: BTW, I'm mastering every day my technique on misreading, misunderstanding & answering everything but what asked... if you want we can share the course :=)
 
SirJB7 and Hui, thank you. Your code works dandy, but only once!


I do this same thing on two different sheets. Exact same code except for the sheet designation. It works fine on the first sheet, does not on the second.


The two pieces of code are in adjacent steps, the two sheets in the same workbook.


Here's an example of the data:


Account Region Mfg Product 2 Branch

1 1 Brown Flux Capacitor LA

1 1 Brown DeLorean LA

2 2 Smith Screwdriver NY


So I want to send seperate sheets to NY and LA with this data, but I don't want them seeing the other's info. I use Autofilter to select everything BUT the designated branch (the MyCriteria variable) and clear it. This leaves only the appropriate data behind.


Then the script does the exact same thing on data in the exact same format on a second sheet.


The filtering code you gave me works perfectly on the first sheet, but not on the second. It's a puzzle!
 
Hi, Hartke!


The code I wrote works with ActiveSheet, so it's independent of worksheet names, you only have to activate the desired sheet first and then run the code. And take care of the fact that data should start at row 9. Does this helps? Otherwise consider uploading proper sample file(s).


Regards!
 
THANK YOU! That did the trick.


I was wrapping the code within the phrase:


' With .Sheets("Name") '


which I thought served to activate and of course I was wrong. Thank you again for your help and your patience.
 
Hi, Hartke!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!
 
You must log in or register to reply here.
Back
Top