+ Reply to Thread
Results 1 to 9 of 9

Search And Delete Routine Based On Conditions

  1. #1
    Registered User
    Join Date
    01-08-2013
    Location
    Triesen, Liechtenstein
    MS-Off Ver
    Excel 2003
    Posts
    12

    Search And Delete Routine Based On Conditions

    Hi there!
    I'm quite new to VBA programming and would greatly appreciate help as nothing I have found so far on the subject really fits.

    Usually my data set consists of a max. of 12,000 rows. I need to delete the entire Row if either of the 2 following conditions are met:
    1) In Column Y it says "Execution Only"
    2) In Column Y it says "No Advice" AND in column F of that same row it says "Appropriateness profile required"
    Other contents are possible but should not lead to any deletions. Using a simple IF... Then approach takes a horrendous amount of time (more tahn 20 mins.). There is however an approach using a select...case statement where the program jumps directly to the next row in which a deletion is to take place, but I wasn't able to figure it out.

    Any help on this would be greatly appreciated!

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Search And Delete Routine Based On Conditions

    Farout,
    Since you have a large data, it would be easier to test the code if you could attach a sample book.

  3. #3
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Search And Delete Routine Based On Conditions

    FarOut,

    Welcome to the forum!
    See if this way is any faster for you:
    Please Login or Register  to view this content.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    01-08-2013
    Location
    Triesen, Liechtenstein
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Search And Delete Routine Based On Conditions

    Hi Tigeravatar,

    I'm overwhelmed! Seems to work like greased lightning... Many thanks.!!

  5. #5
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Search And Delete Routine Based On Conditions

    You're very welcome

    If that takes care of your need, please mark this thread as solved.
    How to mark a thread Solved
    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word "Title" you will see a dropdown with the words "No prefix"
    Change to "Solved"
    Click Save

  6. #6
    Registered User
    Join Date
    01-08-2013
    Location
    Triesen, Liechtenstein
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Search And Delete Routine Based On Conditions

    Will do...I just need to insert this into the larger macro and see if it still works.

  7. #7
    Registered User
    Join Date
    01-08-2013
    Location
    Triesen, Liechtenstein
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Search And Delete Routine Based On Conditions

    Hi Tigeravatar
    Something still baffles me. Your program works fine individually, but as soon as it is inserted into the larger macro the part with the dual condition ("No Advice" + "Appropriateness profile required") doesn't work. If I cut these cells and paste them on a separate Excel Sheet it works again.
    The master sheet hides certain columns that are not needed. Also an Auto-Filter is used. If I show these hidden columns again and deactivate the Auto Filter it works again. Any idea what could be going on here?
    Greets,
    FarOut

  8. #8
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Search And Delete Routine Based On Conditions

    Gary,

    The .Find method will find cells if the rows are hidden normally, but not if they are hidden as a result of a filter. I would recommend adding in a couple of lines to remove any filters before the code runs, and you can reapply the filters after the code is finished.

  9. #9
    Registered User
    Join Date
    01-08-2013
    Location
    Triesen, Liechtenstein
    MS-Off Ver
    Excel 2003
    Posts
    12

    Re: Search And Delete Routine Based On Conditions

    Hi Tigeravatar,

    Works now and I'm thrilled. A big thank you for your speedy help . Have now marked the thread as "solved".

    Best regards,
    FarOut

+ 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