Replace Macro recorded code with more effective code

[scaffdog845]

Hey

I have the following code that was recorded through the Macro record function. The goal was to exclude the cells that had the word "PRE" in it. At the time I recorded the Macro there was only a few things in the list other than "PRE". If someone adds a value not in the code and the Macro is run, the new value is also excluded from the filtered list.

How can I filter $A$1:$P$25001 to exclude only values of "PRE" contained in E:E ?

Here is the code from the Macro

Please Login or Register  to view this content.

[HaHoBe]

Hi, scaffdog845,

AFAIR no direct way to do so but you may consider using either building up a Dictionary of unique items, a list in another sheet to refer to or a helper column to filter by.

Ciao,
Holger

[scaffdog845]

Thanks Holger. I thought just about anything was feasible with VBA and here I though this request would be an especially easy one for the VBA gurus out there.

[HaHoBe]

Hi, scaffdog845,

have you had a look at the Advanced Filter which will perform as you require (sorry I donīt know a way to exclude terms with AutoFilter except what I indicated).

BTW: could you please update your signature - the scales are gone.

Ciao,
Holger

[gsnidow]

scaffdog, you could try something like this. Basically you are adding a column to the right of your data, I usually call it "Exclude", then the macro puts a 1 on each row you want to ignore based on an array of values you supply. In this case, if there are any cells in E:E = "PRE", they will be filtered out. I don't know if you want to look for any instances, i.e. you would filter out a value of "PREVIOUS", because it contains "PRE", or just keep it at exact match. Either way it would be easy to tweak. Now, this is very much a "brute force" method, so it may not be the most efficient, but it works.

Please Login or Register  to view this content.

Greg

[swade730]

Is the ws in this code referring to worksheets?

Please Login or Register  to view this content.

???

[Sbarro79]

How can I filter $A$1:$P$25001 to exclude only values of "PRE" contained in E:E ?

I just wonder: does this

Please Login or Register  to view this content.

solve the problem?

[gsnidow]

I just wonder: does this

Please Login or Register  to view this content.

solve the problem?

Indeed it does work. I only offered the solution I did because it sounded like the conditions were going to grow. I had to import some messy data from a materials database one time, and I literally had to filter out hundreds of values. The only way I could come up with doing it was as I proposed above. However, instead of an array, I simply put my filter out words in another sheet. Anyhow, I think the OP should be okay now.

[scaffdog845]

Hey gang. Thanks for the addition comebacks. I thought I was dead in the water after seeing HaHoBe's response. Fortunately I was able to use a helper column that looked for the value "PRE" and if it existed showed the value defaulted to "HIDE" and if it didn't it defaulted to "SHOW" Pretty simple sort after that.

I'll definitely try the additional suggestions as a learning opportunity.

Take care

[gsnidow]

Is the ws in this code referring to worksheets?

Please Login or Register  to view this content.

???

Yes, and it was referenced in the line above. Probably not necessary here, because it did not save too much typing, but I usually set the reference at the beginning because I may need to reference it later. Just my preference, but not always necessary.

Greg