delete rows not containing certain text

Hi All,

I need a macro to delete all rows which do NOT contain a certain string. The string i would like to specify in a message box, where uppercase or lowercase versions of that string in column G are found I would like the entire row deleted. I need to do this often hence the need for a macro and not filter.

thanks

Hi Alan
Maybe

Please Login or Register  to view this content.

No problem Alan. 4 Eyes see more than 2.
I hope you can do me the same favor sometime. (There probably will be lots of opportunities)

Thanks guys, both versions work on small spreasheets but when i run it on my spreadsheet which is huge (600k rows) it hangs. Also i should mention Column G doesnt just have the value i'm looking for, its part of the string so i assume that will take time as well. Any ideas how i should handle this?

Hi !

Originally Posted by garyb123

so i assume that will take time as well.

No as just operating manually with some Excel basics like any Excel beginner can do
often needs no more than a minute so for casual need a VBA procedure is useless.
And when a VBA procedure is really necessary so just reproducing this easy way …

Attach a workbook in binary mode (.xlsb) with more than 10K rows - the more, the better - in order any helper can give it a try …

With the same assumptions as post #2, applying a filter and deleting visible rows seems pretty quick in my test with 600,000 rows of text data in columns A-Z

Please Login or Register  to view this content.

Don't know about 600K rows. All you can do is try on a copy of your workbook.

Please Login or Register  to view this content.

ByteMarks is faster. It looks to be very similar. Have your pick.

A reminder : as clearing may be faster than deleting … The more non contiguous rows to delete, the slower …

Thanks jolivanes, that worked a treat.

Thanks for letting us know.
Good Luck