Dear All!
Delete entire Row if cell in column contains "Dog" in it.?
Example,
duck321
dog123
cat123
dog123
duck321
cat123
so after it would look like this
duck321
cat123
duck321
cat123
Thanks!
Dear All!
Delete entire Row if cell in column contains "Dog" in it.?
Example,
duck321
dog123
cat123
dog123
duck321
cat123
so after it would look like this
duck321
cat123
duck321
cat123
Thanks!
Re: Delete entire Row if cell in colum contains certain text?
Hi,
Re: Delete entire Row if cell in colum contains certain text?
Thanks Reafidy! it works,
can I add ie. "cat" so that it removes cat and dog from that column?
can I just add dog to your macro?
Re: Delete entire Row if cell in colum contains certain text?
No you cant do that,
I can give you the code to find Dog and Cat if you want but you will have to change the code everytime you want to change the search.
How about an input box?
Sub DeleteRows()
Dim c As Range
Dim SrchRng As Range
Dim SrchStr As String
Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp))
SrchStr = InputBox("Please Enter A Search String")
Do
Set c = SrchRng.Find(SrchStr, LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub
Display More
Re: Delete entire Row if cell in colum contains certain text?
Cool, that works even better.
I have another question (not sure if I need to open a new thread)
how do I remove first 11 characters,
example,
123456789dog
987654321dog
432156789dog
987612345dog
so after it would look like this
dog
dog
dog
dog
Re: Delete entire Row if cell in colum contains certain text?
You could also do this via AutoFilter and set the custom AutoFilter to:
Does not contain: dog
and
Does not contain: cat
Re: Delete entire Row if cell in colum contains certain text?
That question is quite different you should start a new thread and give more details.
Ie will it always be eleven characters, will it always be dog? where are the values to convert, what do you want to do with the results?
Re: Delete entire Row if cell in colum contains certain text?
Yeah, you're right, its better to start a new thread,
it gets off current topic.
Re: Delete entire Row if cell in colum contains certain text?
Quote from micko113Display MoreCool, that works even better.
I have another question (not sure if I need to open a new thread)
how do I remove first 11 characters,
example,
123456789dog
987654321dog
432156789dog
987612345dogso after it would look like this
dog
dog
dog
dog
no loop required
try
Re: Delete entire Row if cell in colum contains certain text?
Quote from jindon
Yep, it does exactly what I need, yay : D
now can you please explain me what does this part do
because later on I wanna change the character limit
Re: Delete entire Row if cell in colum contains certain text?
=Mid(A1,10,9^9)
Retrieving the 9^9 characters from the 10th character in the string.
Re: Delete entire Row if cell in colum contains certain text?
Hmm, I tried to understand it in my own example,
ie.
123456789
with this code 3,7^7 I should be able to get output of 3 in column B
but instead I'm getting 3456789
Re: Delete entire Row if cell in colum contains certain text?
=Mid(A1,10,9^9)
In the above 10 is the tenth character in the string
9^9 is the amount of characters to return. Including the 10th.
This formula =Mid(A1,3,1) will return 3 if A1 is 123456789.
Ie it is returning 1 character after but including the 3 character
This formula =Mid(A1,3,2) will return 34 if A1 is 123456789.
HTH
Re: Delete entire Row if cell in column contains certain text?
I just wanted to Thank all of you who helped me with my problem in this thread, that saved me lot of time also I learned couple of things that will help me later on,
royUK, please stop being an (real) idiot like you called me first in other thread, and stop banning people, you just banned a guy
http://www.ozgrid.com/forum/showthread.php?t=66222&page=2 (pariah07) who you "thought" was me, but that wasn't me, you just banned some guy because in your opinion he wasn't talking nice to you,
I understand I cross-posted I admitted that didn't realize that those forums are linked together and that most users use both forums, but you're policing around like you invented excel, let others determine if they are waisting time with some individual trying to help him, not you, just stop man.
Anyone, feel free to comment before he banns, closes topic or deletes whole thread.
Re: Delete entire Row if cell in column contains certain text?
For information this total idiot Micko has crossposted, ignored code tags and been downright rude. All moderators and MVPs are watching for him and he will be banned whenever he re-appears. he doesn't seem bothered about his crossposting & fails to understand it is the fact that members of all Forums are possibly simultaneously working on a problem that may have already been solved.
pariah was banned for the same reasons, if it wasn't micko it must have been a clone.
Re: Delete entire Row if cell in column contains certain text?
Also, for your information real idiot (royUK),
let me qoute my self, because you didn't got it first time,
Quote from micko114I understand I cross-posted I admitted that didn't realize that those forums are linked together and that most users use both forums,
When you find me in the crowd, make sure to mention you found me, do that after I thank to people who helped me on some issue or I helped someone, then feel free to close that thread/ban me,
Now let's go back to learning excel because I'm not here to explain you something that you will never get it.
Re: Delete entire Row if cell in column contains certain text?
Regardless of whatever transpired in the altercation between royUK and micko115 (I haven't bothered to read into it because I don't have time for it), I believe behaving in the manner micko115 has with his reaction is not acceptable and immature. For all I know micko115 probably has some valid points, however I don't believe the way in which he approached the situation should be tolerated.
There are plenty of excel boards littering cyberspace that have this garbage and troddle flaming on them and I personally joined this one for its zero tolerance policy towards this behaviour.
If you cannot respond in a mature manner, or be the hero and not respond when you think someone is razzing you, then you don't deserve the privilege of being a member on this forum.
Perhaps we could have something in the rules highlighting that there will be no second chances if you choose to take an immature direction with your privileges. But alas...a lot of people don't bother to read the rules.
Re: Delete entire Row if cell in column contains certain text?
This micko character deserves no help at all on any Forum, he crossposted on three different Forums, ignored code tags and was IMO rude. We offer free help on this Board & expect members to follow the Rules. No-one gets banned or locked for no reason especially new members. he has attempted to rejoin several times so he must really appreciate that Ozgrid is one of the best places for Excel help.
I have not yet seen an apology for ignoring the Rules, his quote highlights the fact that he couldn't be bothered to read any Rules because the point on cross posting is made on all Forums, and still does not appreciate the time that may be wasted by his cross posting.
My reputation stands from my posts here and elsewhere.
Re: Delete entire Row if cell in colum contains certain text?
Reafidy,
Thanks so much for your help on this it greatly does get me further. I'm completely new to to Vba and I was wondering if there was a way to modify this code so that it would do the exact opposite (It would not delete "Dog"). Do message me and let me know if a new thread should be started instead.
Quote from Reafidy;342939No you cant do that,
I can give you the code to find Dog and Cat if you want but you will have to change the code everytime you want to change the search.
How about an input box?
Code Display MoreSub DeleteRows() Dim c As Range Dim SrchRng As Range Dim SrchStr As String Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp)) SrchStr = InputBox("Please Enter A Search String") Do Set c = SrchRng.Find(SrchStr, LookIn:=xlValues) If Not c Is Nothing Then c.EntireRow.Delete Loop While Not c Is Nothing End Sub
Don’t have an account yet? Register yourself now and be a part of our community!