Autofilter Macro

[robot]

Hello,

I have a table with a column called "Source". This column can take on any
one of 50 values. I would like to use autofilter to filter out about 30 of
them. Is it possible with autofilter & VBA?

I am thinking about using VBA to concatinate the 20 admissible source names
into one long string (strLong), then use it in an autofilter criterion on
the Source field. More specifically, I hope to find a criterion specifying
that the field value is to be part of strLong. Unfortunately, criterion such
as :="*<string>*" works the other way round (ie it requires the field to
contain a certain string). So I got stuck.

Suggestions are most welcome! (I use Excel XP).

 

[Joel]

It is probably better to use Advance Filter which has an option to have a
criteria range of multiple items. Try using Advance filtering from the
worksheet menu to seee how it works. another choice is to create you own
filtering using arrays

MyArray = Array("Item 1", "Item 2", "Item 3", "Item 4")

for each itm in MyArray

'add your code here
next itm

 

[Stefi]

One way is to use a helper column. Place strLong in a cell (in my example
G2), place formula
=ISNUMBER(SEARCH(F2,$G$2))
in first cell of the helper column (in my example F2), fill it down as
required, and Autofilter column F for TRUE.
Regards,
Stefi


„robot†ezt írta:

 

[robot]

Thank you for your reply.

Actually I have used autofilter on other columns, so I think I'll stick to
autofilter in the mean time.

Filtering by array? How do I go about doing that? Grateful if you would
provide more details. Thanks again.

 

[robot]

Dear Stefi,

I'll try that. Thanks for your reply.

 

[Joel]

If you arre going to use Stefi's suggestion of creating a helper column then
the helper column becomes the criteria for the Advance filter. After you
generate the helper column try manually on the worksheet and use the Advance
Filter. If you lkike the results then simply record a macro while performing
the Advance filter and then copy the recorded code into your macro.