+ Reply to Thread
Results 1 to 6 of 6

Delete a row if a cell contains certain text as part of a text string

  1. #1
    Registered User
    Join Date
    11-17-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Delete a row if a cell contains certain text as part of a text string

    Hello, I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell. Any ideas?
    Last edited by Steve2107; 05-16-2014 at 03:12 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Delete a row if a cell contains certain text as part of a text string

    for r = usedrange.rows.count to 1 step -1
    if instr(1,cells(r,1),"Adj sub")=0 then
    rows(r).select
    selection.entirerow.delete
    next r

    NOT TESTED
    Hope this helps

    Sometimes its best to start at the beginning and learn VBA & Excel.

    Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
    Available for remote consultancy work PM me

  3. #3
    Registered User
    Join Date
    11-17-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Delete a row if a cell contains certain text as part of a text string

    Thanks Nathan, what is "instr" in the code?

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: Delete a row if a cell contains certain text as part of a text string

    In String checks (number of start character, string to search, string to search for)

  5. #5
    Registered User
    Join Date
    11-17-2010
    Location
    Australia
    MS-Off Ver
    Excel 2007
    Posts
    69

    Re: Delete a row if a cell contains certain text as part of a text string

    Thanks again Nathan, so this "Ïn String" is some sort of keyword? I just have never seen it before that's all. I am learning some of this as I go so I am probably asking a bit of a vague question.

  6. #6
    Forum Contributor
    Join Date
    01-25-2011
    Location
    Virginia
    MS-Off Ver
    Excel 2016
    Posts
    166

    Re: Delete a row if a cell contains certain text as part of a text string

    InStr is a VBA function, it is the equivalent of Excel's Find Function.

    Regards,
    Tom

    If my answer resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post
    .

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. delete middle part of text string
    By maacmaac in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-30-2009, 03:59 PM
  2. Finding part of a text string in a cell
    By Tonro in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-18-2008, 05:59 PM
  3. removing part of text string to another cell
    By jamie_k in forum Excel General
    Replies: 2
    Last Post: 07-24-2006, 06:00 AM
  4. [SOLVED] Use a formula to delete part of a text string?
    By Josh Craig in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-29-2006, 03:25 AM
  5. Replies: 3
    Last Post: 12-09-2005, 03:10 PM

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