+ Reply to Thread
Results 1 to 8 of 8

How do I delete all rows containing a certain (variable) text string?

  1. #1
    Registered User
    Join Date
    01-14-2008
    Posts
    2

    How do I delete all rows containing a certain (variable) text string?

    Hello. I apologise if this topic has been covered before but I've had a look and cannot find an answer on this forum.

    I often spend a lot of time sorting through a large spreadsheet of data and cutting out certain rows I know to be unnecessary based on a word or part of word they contain.
    I would be grateful if someone could suggest a method to search through a spready and delete any row contaning a certain string.

    For example, if I had:

    twotwothree
    oneonetwo
    twothreeone
    onetwotwo
    threethreeone

    and wanted to get rid of any row containing 'three' to leave just

    oneonetwo
    onetwotwo

    The data I need to search for and remove is always different so it would be ideal to have an input box to prompt for the string to search for, so in the above example for eg. you could specify 'two' instead of 'three'. In addition the data is not always in the same column.

    Please help! Going through each and every row rightclick-deleting irrelevant ones is driving me crazy. I've scoured the internet for solutions to this, and I've found various 'solutions' that appear to be ideal, but when I try to use them nothing ever happens (well, one deleted absolutely everything). I have a general idea of coding but no clue about Excel VBA, which makes me all the more frustrated when it looks like something should work but I'm at a loss to work out why it doesn't. I'm using Excel 2003, if that makes any difference at all.

    Thanks in advance.

  2. #2
    Registered User
    Join Date
    01-07-2008
    Posts
    38

    Try this

    Give this a try. Highlight the cells or column which contains the strings to be matched and then invoke this macro.

    Chuck

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    01-14-2008
    Posts
    2
    Thanks! That's worked 100% better than anything else I've tried so far. However it seems to only work if the data is in column A, but not if the data is in any other column. In my current situation it's in column E. That's easily solved - I've just manually swapped over columns A and E and then swapped them back once the deletions are made.

    Much appreciated. When I have some spare time I'll try to see if I can tweak it so I don't have to put the relevant data into column A.

  4. #4
    Registered User
    Join Date
    01-07-2008
    Posts
    38

    Select a range

    Hi. An additional note: if you select a range (like column E, or cells in column E) then invoke the macro, it should work without a code change.

    Chuck L

  5. #5
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    With this code you don't need to select anything, either manually or within the code

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  6. #6
    Registered User
    Join Date
    07-28-2012
    Location
    Stockholm
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: How do I delete all rows containing a certain (variable) text string?

    I need a litle help... This is my first post and I`m beginner in exel....
    I have list off all e-mail address's that I buy from marketing agency.
    I need some script that filter and select all rows with matched string (.net for example)

    Thanks in advance

  7. #7
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: How do I delete all rows containing a certain (variable) text string?

    You need to start your own question

  8. #8
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: How do I delete all rows containing a certain (variable) text string?

    Thanks Roy,

    Sashamilic,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  9. #9
    Registered User
    Join Date
    02-22-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1

    Re: How do I delete all rows containing a certain (variable) text string?

    HOLY SMOKES RoyUK. That rocked!!! It works so fast and perfectly. Thank you for sharing your time, knowledge and expertise. I'm immensely grateful.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1