+ Reply to Thread
Results 1 to 17 of 17

Delete Rows if a cell does not equal "Finished" or "Complete"

  1. #1
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Delete Rows if a cell does not equal "Finished" or "Complete"

    Can anyone help me create a macro that will delete cells based on the criteria above. And run a loop that will check only cells with data in them so it doesn't try and go through every existing row. The cells I want checked is "F".

    Thank you to anyone willing to take a look at this.

    -Justair
    Attached Files Attached Files

  2. #2
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Can you attach a workbook that contains sample data for us to work with...so we can tailor possible solutions for you?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    I forgot to add one in my original post, it should be attached now.

    Thank you,

  4. #4
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Hi Justair,

    try the code below - on your sample it worked fine.

    Please Login or Register  to view this content.
    Please click the * below if this helps
    Please click the * below if this helps

  5. #5
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Awesome thank you, but is there a way that The first column can be excluded. I forgot about my headers.

  6. #6
    Forum Contributor
    Join Date
    07-17-2012
    Location
    India
    MS-Off Ver
    Excel 2007, 2010
    Posts
    694

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Replace this

    Please Login or Register  to view this content.
    with

    Please Login or Register  to view this content.
    This would just not add the Insert row. But avoiding deletion of header in Jasper code, I need Jasper help.
    Last edited by shiva_reshs; 09-19-2013 at 03:57 PM.
    Keep the Forum clean :


    1. Use [ code ] code tags [ /code ]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    2. Show appreciation to those who have helped you by clicking * Add Reputation below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

  7. #7
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    That still deletes the first column, i even tried:
    Please Login or Register  to view this content.

  8. #8
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Usually headers are in the first ROW, not in the first COLUMN, but anyway.

    This will keep the data in the first column:
    Please Login or Register  to view this content.
    and this will keep the first row
    Please Login or Register  to view this content.
    Please click the * below if this helps

  9. #9
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Perfect, thank you JasperD!!

  10. #10
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Jasper, The code seems to take a very long time to run. Do you if there is a way to improve the speed?

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

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    I could not imagine a loop could be faster than a filter, but try the attached.
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    AB33, lighting fast!! Only problem is it deletes my headers in Row 1

  13. #13
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Quote Originally Posted by Justair07 View Post
    AB33, lighting fast!! Only problem is it deletes my headers in Row 1
    And re-formats my columns?

  14. #14
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Quote Originally Posted by Justair07 View Post
    AB33, lighting fast!! Only problem is it deletes my headers in Row 1
    And re-formats my columns?

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

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Change this line

    Please Login or Register  to view this content.
    INTO

    Please Login or Register  to view this content.
    Please explain what do you mean by re-formats my columns?

  16. #16
    Forum Expert JasperD's Avatar
    Join Date
    05-07-2013
    Location
    Netherlands
    MS-Off Ver
    Excel 2016
    Posts
    1,393

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    How on earth can a loop be quicker than a filter... ?

  17. #17
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Awesome thanks for the help, works great!!

  18. #18
    Forum Contributor
    Join Date
    01-08-2013
    Location
    Jacksonville, Fl
    MS-Off Ver
    Excel 2016
    Posts
    355

    Re: Delete Rows if a cell does not equal "Finished" or "Complete"

    Quote Originally Posted by JasperD View Post
    How on earth can a loop be quicker than a filter... ?
    I'm not sure but it's almost instant compared to 2 minutes. I'm not smart enough to understand why.

+ 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. [SOLVED] If there is any text in column "A$" on "sheet1" then move cell to column "A$" on "sheet2"
    By ckgeary in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-27-2013, 08:28 PM
  2. [SOLVED] Cell Formula to count time with "Greater Than or Equal to" and "Less Than"
    By chriswhite1982 in forum Excel General
    Replies: 3
    Last Post: 06-16-2013, 12:30 AM
  3. "Form and macro" to delete "Active" cell contents.
    By wanty in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-22-2013, 11:33 PM
  4. [SOLVED] Delete row if cell in Column N equal "Different" or Column P equals "Blank"
    By Cyberpawz in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2012, 08:25 AM
  5. Delete rows in the result of formulas "OK" n column "J"
    By marreco in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-29-2012, 10:08 AM

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