+ Reply to Thread
Results 1 to 10 of 10

Macro to delete rows that DO NOT contain specific word

  1. #1
    Registered User
    Join Date
    03-19-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Macro to delete rows that DO NOT contain specific word

    Hi all,
    I am very new to macros in Excel and VBA code, so excuse me if this is a basic request.

    I have a spreadsheet from columns A to P, no longer than probably 1000 rows. In column P, a large number of cells have TINVGARS in them.

    I would like to create a macro in order to delete all rows that DO NOT contain TINVGARS, thus leaving me with a clean spreadsheet with only those rows with TINVGARS in Column P left.

    Is this possible at all?

    Many Thanks for your help!

    Steve

  2. #2
    Registered User
    Join Date
    03-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Macro to delete rows that DO NOT contain specific word

    Open VB Editor, create a new module and dump this into it.
    Then it should appear in your Macros section on the spreadsheet.
    Make sure you on the sheet you want this to run on.

    Please Login or Register  to view this content.
    Last edited by AntrobusJ; 03-19-2013 at 10:43 AM. Reason: Used wrong BCODE
    Please use the star icon to rate my answer.

  3. #3
    Forum Contributor ragavan.sridar1's Avatar
    Join Date
    11-19-2012
    Location
    India
    MS-Off Ver
    Excel 2010, Excel 2003
    Posts
    208

    Re: Macro to delete rows that DO NOT contain specific word

    you can try this also..

    just another thought.

    Please Login or Register  to view this content.
    Thanks!
    Raga.

    Please,mark your thread [SOLVED] if you received your answer.

    Click the little star * below, to give some Rep if you think an answer deserves it.

    I learnt so many things from these links.

  4. #4
    Registered User
    Join Date
    03-19-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Macro to delete rows that DO NOT contain specific word

    Thanks ragavan.sridar, that code gives me a Run Time Error - it seems to not like, as these are highlighted when I click Debug:-

    Sheets("Sheet1").Range("A1").AutoFilter _
    Field:=16, _
    Criteria1:="<>" & "TINVGARS", visibleDropDown:=False

  5. #5
    Registered User
    Join Date
    03-19-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Macro to delete rows that DO NOT contain specific word

    Thanks Antrobus, could you put that in full VBA code so I can try and copy and paste? This is the first VBA code I've used! Many Thanks!

  6. #6
    Valued Forum Contributor Sean Thomas's Avatar
    Join Date
    03-25-2012
    Location
    HerneBay, Kent, UK
    MS-Off Ver
    Excel 2007,2016
    Posts
    971

    Re: Macro to delete rows that DO NOT contain specific word

    this checks from last row up

    Please Login or Register  to view this content.
    Regards
    Sean

    Please add to my reputation if you think i helped
    (click on the star below the post)
    Mark threads as "Solved" if you have your answer
    (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code:
    [code] Your code here [code]
    Please supply a workbook containing example Data:
    It makes its easier to answer your problem & saves time!

  7. #7
    Registered User
    Join Date
    03-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Macro to delete rows that DO NOT contain specific word

    Quote Originally Posted by AntrobusJ View Post
    Open VB Editor, create a new module and dump this into it.
    Then it should appear in your Macros section on the spreadsheet.
    Make sure you on the sheet you want this to run on.

    Please Login or Register  to view this content.
    I had amended it. Sean's is a better solution if you want it to automatically detect the last row, if you want to specify what rows to go up to then use mine and change the 1000 to how many rows you want it to do.

  8. #8
    Registered User
    Join Date
    03-19-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Macro to delete rows that DO NOT contain specific word

    Thanks Sean - perfect!

  9. #9
    Registered User
    Join Date
    03-19-2013
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    6

    Re: Macro to delete rows that DO NOT contain specific word

    You've all been a fantastic help many thanks!

  10. #10
    Registered User
    Join Date
    03-19-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    17

    Re: Macro to delete rows that DO NOT contain specific word

    Your welcome, please mark your thread as solved and give Sean reputation
    (read Sean's Signature)

+ 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