Clear cells with specific text in them

Please Login or Register  to view this content.

This question really should be a FAQ. I see it time and time again and looping just isn't the best way of doing this sort of thing.

Whenever you find yourself creating a loop step back a bit and consider if there isn't a more efficient way. Loops should only be used as a last resort since they can be very slow particularly when many rows are involved.

The fastest and most efficient way I know of doing this is to use an autofilter and filter for whatever is the relevant criteria. Then select all the filtered rows using the .SpecialCells(xlCellTypeVisible) construct, delete them all, finally remove the autofilter.

In your case, untested in the absence of the workbook but try

Please Login or Register  to view this content.

Hi,

@stnkynts
Fair point and I accept that if the OP meant (and re-reading his original I think you're correct) that the column contained all sorts of labels and blanks and only blanks should be deleted then as stated it would delete all apart from Company Code. In which case the filter line might have been better as

Please Login or Register  to view this content.

but having seen John's remark about 'contains' he's reminded me of something that I have a vague recollection of reading before, so from me too - thanks John.

As for working with and editing code that's given: It's a moot point. I agree that should be the default position but when I see code that is badly written or redundant, or where there are lots of .Selects .Activates and other stuff that is clearly from the macro recorder I believe it's important to not only indicate there are more efficient ways but also show how. The looping thing to determine whether to process a particular row is a particular bete noir of mine and something that impressed me very early on when I was in the corporate world and handled some large tables. I used to waste a lot of time waiting for macros to complete until someone mentioned the filtering method which enables whole chunks of data to be processed immediately. My life was transformed and I've never forgotten it. If we were ever asked for a top tip that would be high on my list.