Clean up code for conditional deletion of columns

**JChandler22** · 03-18-2010, 05:45 PM

Hi All,

I have the following code that I'm thinking can be made cleaner and more efficient, but I'm not quite sure how:

Please Login or Register  to view this content.

Is there a way to rework this so that each cell doesn't actually have to be selected? I'm guessing that this might slow the macro down.

Many thanks in advance,
Jeff

**Richard Buttrey** · 03-18-2010, 06:05 PM

Hi Jeff,

Rather than a long loop that examines every cell, a much more efficient way quicker way is to simply filter the data to show all rows that are not equal to contractor, then select and delete all those filtered rows.

HTH

**JBeaucaire** · 03-18-2010, 06:13 PM

Your original code doesn't even work correctly as designed. If there are two rows together that both have "contractor" in column D, the second one won't be deleted.

Indeed, using the autofilter to do it all at once is the way to go.

**JChandler22** · 03-19-2010, 10:50 AM

Awesome - thank you both. I use Excel's auto filter feature religoiusly (ctrl + shift + L is one of my first commands when opening any spreadsheet).

The snippet of code above is part of a larger macro, and that macro will be used by several of my colleagues who are less than tech-savvy. I want to have a magic button for them to push rather than teaching them about auto filters (among other things). What I hadn't considered - and thanks for showing me the light - is that I can integrate the auto filter feature into the macro instead of using the long loop. This will be much more efficient.

Thanks again.