Delete Rows not containing a word

Inelegant, because it just moves all instances of "DOG" to sheet2, but if that solves your problem then okay... If not, let me know, I'll make it more accurate (if you can't adapt it yourself)

Please Login or Register  to view this content.

Make sure to change DOG to whatever string you're looking for (the *s are wildcards, and make sure that if you have jawoeifjaowiefjDOGjdoafijeawoijf it still pulls it out, but this method is case sensitive, i.e. it won't pull dajofiewdogajoweifj or dog)



Update: This new code will do what you want more specifically, if you'd like

Please Login or Register  to view this content.

if you want all your DOG cells to be consecutive after the deletion change the line:
.Cells(rowcount, 1).ClearContents
TO:
.Cells(rowcount, 1).entirerow.delete
Though that's if you don't care about associated data in the same row as your cleared values. It's up to you...

Thank you it works great I used:

Please Login or Register  to view this content.

The only thing is it doesn't delete the entire row that doesn't contain the word dog, I tried replacing it like you said with .Cells(rowcount, 4).entirerow.delete but the screen keeps twitching it does it for 10 minutes and nothing changes.... I used this instead..
1.Select the Colum (Column D)
2.Pressed F5, Special--> Blanks--> OK.
3.Ctrl-Minus and select Entire Row.

Sorry ryan, I usually only look at this on weekdays, hence the late response. But let me ask some troubleshooting questions (again, this is tough not seeing your worksheet, though I understand not wanting to post something from work).


0.5) If clearcontents is working and the troubleshooting below doesn't work, try adding this to the end of the code (does what you do manually, but in vba):

Please Login or Register  to view this content.

I'm obviously not really in favor of this method though, because you should just be able to skip directly to the deleting entire rows via the code I already came up with. But if it works, it works!
1) (rowcount, 4) refers to an iteration of row, and 4 refers to column D, so if "dog" isn't in column D then you have to change "4" to whatever number column it is in. Also make sure it is Sheet1 that you're using, because the code says "With Sheet1" (I know it's a simple thing, but I'm just trying to think of anything that could be going wrong at all so that I exhaust almost all avenues of inquiry).
2) The "like" form that I use for this code is case sensitive, so if you enter in "DOG" and you're looking for "dog" it won't find it
3) Make sure you didn't delete the *s surrounding "*DOG*". They are wildcards, and probably important for what you're doing.
4) If you're working with an incredible amount of data, delete entire row isn't the way to go, I think. If clearcontents serves your purpose I'd recommend using that instead. But if not ---->
5) Try adding "application.screenupdating = false" to the start of the code, then at the end "application.screenupdating = true" Also consider checking this out for turning off calculation, I can't remember the way to do it so I just googled it: http://www.ozgrid.com/VBA/calc-stop.htm
6) The code shouldn't take more than a minute, even if it is stupidly inefficient. Most code this length should take under 10 seconds. If it's taking longer than that, it's probably caught in some loop, so hit shift+esc to stop the code, then starting at the top hit f8 to step through the code and see which section is taking so long/making your screen flicker

That's all I can think of right now.... The code I posted worked for me in a simple test worksheet I came up with, but I have no idea how my test worksheet differs from your actual worksheet (probably in a lot of ways). Let me know how things go, if I don't check this tomorrow I'll certainly check it by Monday.

Also, arthurb, that's not my code that you reposted there (evidenced primarily by the fact that I don't know how to use "case" code), so I don't know. The code that I did post moves data to another worksheet because it specifices to do so in this line (at the very start):

Please Login or Register  to view this content.