Delete entire Row if cell in column contains certain text?

  • 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?


    Code
    Sub DeleteRows() 
        Dim c As Range 
        Dim SrchRng 
         
        Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("A65536").End(xlUp)) 
        Do 
            Set c = SrchRng.Find("Dog","Cat" LookIn:=xlValues) 
           If Not c Is Nothing Then c.EntireRow.Delete 
        Loop While Not c Is Nothing 
    End Sub
  • 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?


  • 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?


    Quote from bradles

    You could also do this via AutoFilter and set the custom AutoFilter to:


    Does not contain: dog
    and
    Does not contain: cat


    I tried that but it doesn't do a good job as Reafidy's suggestion.

  • 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?



    no loop required
    try

    Code
    Sub test()
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Offset(, 1).Value = Evaluate("if(row(" & .Address & "),mid(" & .Address & ",10,9^9))")
    End With
    End Sub
  • Re: Delete entire Row if cell in colum contains certain text?


    Quote from jindon

    no loop required
    try

    Code
    Sub test()
    With Range("a1", Range("a" & Rows.Count).End(xlUp))
        .Offset(, 1).Value = Evaluate("if(row(" & .Address & "),mid(" & .Address & ",10,9^9))")
    End With
    End Sub


    Yep, it does exactly what I need, yay : D


    now can you please explain me what does this part do

    Code
    ",10,9^9))")


    because later on I wanna change the character limit

  • 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 micko114

    I 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.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!