Best way to search/filter a column?

[Kobayashi]

I have a s/sheet that contains in excess of 1 thousand rows. Column A
has an index I wish to use to sort and remove data and this contains
approx. 100 unique strings, of which I wish to search for approx. 30
and delete the rows that do not match any of the 30.

So, does anybody know the best way to do this?

Autofilter?
For each.. and multiple If statements?
For... and multiple If statements?
Select case statements?

I'm happy to try and work the code out (although if anybody has any to
hand that would be even better!) but just don't want to waste time
creating slow and inefficient code to start with so if anybody could
tell me which method is the best I would much appreciate it?

Regards,

Adrian

 

[Dave Ramage]

Adrian,

I'd use the Advanced Filter. For the criteria range,
specify a range containing the list of strings you want to
filter for with the field name as the first row.

To automate this, use the macro recorder while you do it.

Cheers,
Dave

 

[Kobayashi]

Please, I'm only looking for direction as to which method to follow?
I'll do the rest.

Thanks,

Adrian

 

[Kobayashi]

Dave, thanks but could you take a look at my releated post above and see
if you can help?

Regards,

Adrian

 

[Dave Peterson]

How about putting that list in another worksheet.

then insert a new column that looks to see if the adjacent cell matches that
list.

=if(isnumber(match(b1,sheet2!a:a,0)),"Match","no Match")
and fill down.

(I'm not sure if the list will be the keep list or the delete list.)

Then sort your data by that column, apply data|filter|autofilter and delete the
ones you want. Then delete that helper column.

If your rows to be deleted are contiguous, then the delete will work quicker.

 

[Kobayashi]

Dave,

Many thanks. I've tinkered and, taking your advice, got things
working.

Thanks again,

Adrian