+ Reply to Thread
Results 1 to 16 of 16

Macro or Formula to delete entire row based on multiple criteria

  1. #1
    Registered User
    Join Date
    01-04-2020
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    36

    Macro or Formula to delete entire row based on multiple criteria

    Hello,

    I posted a similar thread to this that has since been solved, but now I have a different issue that is similar, but not the same.

    The criteria I am looking for is every instance where there is a "1" in the second column and an "N" in the third column. In this scenario I would want the entire row deleted, as well as all rows where column A matches what was just deleted. For example in my attachment, I would want everything that is highlighted to be deleted. To go further in depth, I would want everything with a "123" in column A to be deleted since there was a "1" in column B and an "N" in column C. Is there a formula or macro that would accomplish this? Thank you very much!
    Attached Files Attached Files

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Macro or Formula to delete entire row based on multiple criteria

    You could put this formula in Column C
    =COUNTIFS(A:A,A2,B:B,1,C:C,"N")>=1

    Then autofilter the data based on col C being true, then delete the visible rows.

  3. #3
    Registered User
    Join Date
    01-04-2020
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    36
    Quote Originally Posted by Fluff13 View Post
    You could put this formula in Column C
    =COUNTIFS(A:A,A2,B:B,1,C:C,"N")>=1

    Then autofilter the data based on col C being true, then delete the visible rows.
    Do you mean to put that formula in column D?

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Macro or Formula to delete entire row based on multiple criteria

    Oops, yes I did.

  5. #5
    Registered User
    Join Date
    01-04-2020
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro or Formula to delete entire row based on multiple criteria

    Thank you for your help! Do you know any simple macros that could run that formula and then delete everything that says "True"?

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Macro or Formula to delete entire row based on multiple criteria

    Yup, like
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-04-2020
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro or Formula to delete entire row based on multiple criteria

    Quote Originally Posted by Fluff13 View Post
    Yup, like
    Please Login or Register  to view this content.
    I'm sorry but what if columns B and C were actually columns AD and AE, respectively? Thank you!

  8. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Macro or Formula to delete entire row based on multiple criteria

    Which column would the formula go in and is col A still col A?

  9. #9
    Registered User
    Join Date
    01-04-2020
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro or Formula to delete entire row based on multiple criteria

    The formula would go in column AF and yes, column A is still column A. Does this macro require that the formula is in before running the macro?
    Last edited by lucasc10; 01-05-2020 at 04:45 PM. Reason: Changed to AF

  10. #10
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Macro or Formula to delete entire row based on multiple criteria

    Try
    Please Login or Register  to view this content.
    And no, you don't need to put the formula in first.

  11. #11
    Registered User
    Join Date
    01-04-2020
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro or Formula to delete entire row based on multiple criteria

    This is the error I got...
    "Run Time error 1004"
    Application-defined or object-defined error."
    Attachment 656497

  12. #12
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Macro or Formula to delete entire row based on multiple criteria

    Which line gave that error?

    The attachment is giving me an Invalid error.

  13. #13
    Registered User
    Join Date
    01-04-2020
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro or Formula to delete entire row based on multiple criteria

    It starts with ".value=Evaluate"

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Macro or Formula to delete entire row based on multiple criteria

    Just noticed you changed the column to AF, try
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    01-04-2020
    Location
    America
    MS-Off Ver
    Excel 2010
    Posts
    36

    Re: Macro or Formula to delete entire row based on multiple criteria

    Quote Originally Posted by Fluff13 View Post
    Just noticed you changed the column to AF, try
    Please Login or Register  to view this content.
    Thank you very much!
    Last edited by lucasc10; 01-05-2020 at 05:04 PM. Reason: It worked, never mind

  16. #16
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: Macro or Formula to delete entire row based on multiple criteria

    You're welcome & thanks for the feedback

+ 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. Formula to delete entire row if multiple criteria is met
    By lucasc10 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-04-2020, 12:56 PM
  2. [SOLVED] Macro to Delete Entire Row Based on Formula in Given Column
    By datutt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-19-2015, 10:12 PM
  3. [SOLVED] Delete entire row based on 2 criteria
    By coopman64 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-13-2012, 03:38 PM
  4. [SOLVED] Sum multiple entire rows based on criteria, array formula challenge!
    By ppffffpp in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-10-2012, 09:42 PM
  5. Delete entire row based on criteria
    By D_N_L in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-08-2011, 09:13 AM
  6. Delete entire row from range based on criteria
    By uncleslinky in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-29-2011, 06:10 AM
  7. delete entire rows based on a criteria
    By acekargo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-30-2008, 11:15 PM

Tags for this Thread

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