+ Reply to Thread
Page 1 of 2 1 2 LastLast
Results 1 to 15 of 16

VBA code to search for values and delete a row

  1. #1
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    8

    VBA code to search for values and delete a row

    Hi all. Happy New Year. I am new to this forum and not very skilled with VBA. I have a worksheet that is not very big. I need to search it and see if a specific value (for this example just use abc123) exists in column A. If this value exists and a value of 'census' exists in the row immediately below it, I want to delete the entire row that has 'census' in it.

    So... if 'abc123' exists in column A and 'census' exists in the row right below it, delete the row with 'census' in it.

    I am guessing a for - next loop and some if statements need to be involved but I am not sure how to set it up. I have spent some time on Google but have not found exactly what I need.

    Thank you!!

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,744

    Re: VBA code to search for values and delete a row

    Try this

    Please Login or Register  to view this content.
    Open up the VBA editor by hitting ALT F11

    Insert a new module by hitting Insert - Module

    Paste the macro into the empty sheet

    Hit ALT F11 to get back to the worksheet.

    Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007/2010/2013.
    Martin

    If my solution has saved you time and/or money, please consider donating to Cancer Research UK or your local equivalent.

    https://www.cancerresearchuk.org/

  3. #3
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    8

    Re: VBA code to search for values and delete a row

    Thank you for the quick response. Does this find 'abc123' in a cell and then find 'census' in the cell right below it and delete the row with census in it?

  4. #4
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,744

    Re: VBA code to search for values and delete a row

    The code starts at the bottom and works its way upwards. If it finds a row containing 'census' in any column, it deletes this row and the one above it.

    Have I understood your question correctly? - I suspect not.

  5. #5
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    8

    Re: VBA code to search for values and delete a row

    No. I do not want every instance of census deleted. I only want certain rows that start with 'census' deleted. And I do not want the row above it deleted. It depends on the value in the cell immediately above it whether or not I want the row with 'census' deleted.

  6. #6
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,484

    Re: VBA code to search for values and delete a row

    ngenova,
    When you say a row has census, do you mean in column A? or any column? Also, will census be IN the value of the cell in the row below? Or will it be part of a larger text in said cell?
    Should "abc123" be part of the code, or should there be a cell you input the search value in, or should a prompt come up to ask what the search value is? Also, again, will abc123 be the only value in the cell in column A? or be part of greater text?
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

  7. #7
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    8

    Re: VBA code to search for values and delete a row

    'census' will be the entire code in column A only. 'abc123' will be the entire code in column A only.

    Examples:

    cell A72 = abc123
    cell A73 = census

    in this instance I want row 73 deleted




    cell A51 = 7878aaaa
    cell A52 = census

    in this instance I DO NOT want row 52 deleted
    Last edited by ngenova; 01-03-2018 at 04:02 PM.

  8. #8
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,484

    Re: VBA code to search for values and delete a row

    Give this a try:

    Please Login or Register  to view this content.
    P.S. I'd avoid for loops if possible for this kind of action on large sheets, but as you said it is not very big, this was the simplest solution.
    Last edited by Arkadi; 01-03-2018 at 04:13 PM.

  9. #9
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    8

    Re: VBA code to search for values and delete a row

    Great, that works! Can I put multiple values in the mySearch variable (ex - "abc123" or "777ttt" or "rrr333") or do I need to do a separate sub for each different value?

  10. #10
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,484

    Re: VBA code to search for values and delete a row

    You don't need a different sub for each, but if you want to search for multiple values, you will need a few modifications.

  11. #11
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    8

    Re: VBA code to search for values and delete a row

    Got it, thanks so much!!

  12. #12
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,484

    Re: VBA code to search for values and delete a row

    I think this version will work for you with multiple search words:

    Please Login or Register  to view this content.

  13. #13
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    8

    Re: VBA code to search for values and delete a row

    Ok, cool. Can a wildcard be used with the search as well? I would like to know if I need this in the future.

    ex. Instead of "abc123" could it be "abc*" or something similar if the code STARTED with abc?

  14. #14
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Excel 2010
    Posts
    3,484

    Re: VBA code to search for values and delete a row

    If you want to use wildcards, you will need the Like argument instead of "=". Like will still work for exact matches, so the example below finds anything that starts with abc, and also exact matches of 777ttt and rrr333:

    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    01-03-2018
    Location
    Ohio
    MS-Off Ver
    2010
    Posts
    8

    Re: VBA code to search for values and delete a row

    Awesome!! Thank you so much!!

+ Reply to Thread
Page 1 of 2 1 2 LastLast

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