How to remove ALL rows that DO NOT have a match for certain word or number

Assuming row1 is your titles and data starts at row2... the AutoFilter is perfect for this. This macro will the AutoFilter on column J to hide rows that have the word "sacked", deletes all the rest all at once.

Please Login or Register  to view this content.

or ?

Please Login or Register  to view this content.

SNB, you're basically making the same suggestion I did, except you've introduced the "special cells" method unnecessarily. With the limitation of 8500 "areas" in special cells, it should never be used on large data sets (40k rows) when a simple alternate method can be used that will avoid the built in limitation altogether.

Also, your code simply ignores the fact that other prior Autofilters might break the code.

Respectfully, offering the same technique with fewer lines of code isn't always necessary. And in this case is definitely not safer.

I hate "looping" code over and over to do the same evaluation, the AutoFilter makes that possible to skip, many times.

Your original question was for a single "text string match" against a single column. Perfect for autofilter, one time, straight shot, done.

Your new request is not so simple. Your mind views "for 10 yards"...."for 100 yards" as a simple step, but you have really added 90 new "strings". They're all different.

So I can't really picture a way to NOT run the AutoFilter 90 separate times, which is fine to do, but deleting rows 90 times instead of all at once....what a chore.

You said "row J" which doesn't mean anything. I presume you meant to change to another column, so I underlined the number you need to edit to match the column number you want to evaluate. I made it column K for now.

Please Login or Register  to view this content.

I might have explained it wrong. In the 2nd question I no longer care about the word "sacked" as that task is done. What I am looking for now is to keep rows that have the phrase "for 10 yards" or greater all the way up to "for 100 yards" in column J.

Sorry I if I did not explain it correctly. You are great for helping!

Dave

Did you try simply removing all that stuff relating to sacked?

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Sorry, SNB, I repeat my concern with applying SpecialCells to a dataset known to be 40k rows deep. Anyway...

I would echo JB's comment. If you're learning to code, adopt code you can read, understand, and learn from.