Autofilter Sucks!

[Err]

Originally I posted a topic on this board which was moved here:
http://www.mrexcel.com/board2/viewtopic.php?t=248065

It was moved (I think) because the moderator thought I was asking a very obvious question (which I frequently do) -which is how to get autofilter to work.

In that thread, I think the answerers were being very defensive about Microsofts workaround which is basically -"don't use autofilter on numeric data". Furthermore, within the context of "asking for help"; people are going to give you the easiest way that they know how and not really discuss the issue -which is what I really wanted when I posted that thread here.

I'm not trying to be antagonistic here. But realistically -when you are running VBA -it is kinda ugly to add a leading Letter (I like "A") to every field before you autofilter it; then trim it off after the filter. It's ugly code because it's adding a 2 extra loops. If you have this (or several) autofilters inside nested loops then you are really slowing things down.

So here is a question -since I rarely ever have used Advanced Filter; does it have the same bug? Can you filter on numeric data specifying the begining characters? Can you specify a date range?

-Err

 

[Oaktree]

Please see my post in http://www.mrexcel.com/board2/viewtopic.php?t=248065.

 

[SydneyGeek]

When filtering in code I prefer to use Advanced Filter. It doesn't have the same limitations that Autofilter does.
If you want to filter a number range, the trick is to set up the criteria section with 2 columns for the number. One will have >= the low value, the other will have <= the high value.
I park the Advanced Filter criteria away from the other data (you can hide the columns if you like).
Also, with dates: low value, high value as above. I *always* convert a date to its numeric value (using CDbl) for filtering. Keeps me sane.

Denis