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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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
-----Original Message-----
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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

.

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

Thanks,

Adrian



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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

Regards,

Adrian



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

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 wrote:

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

Thanks,

Adrian

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

--

Dave Peterson

Dave,

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

Thanks again,

Adrian



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/