Code or Formula to delete rows based on multiple criteria

[Lucasc10]

Hello all!

I am looking for a code or formula to delete the entire row if it meets the following conditions... I would like the entire row deleted if "Column B" is the first instance (by column A) and it correlates to an "N" in "Column C". I would also like every row that contains the same number in "Column A" as the one that was deleted to be deleted. I know that this is confusing, so I will explain my example sheet below...

I would want everything that is highlighted in the example to have the whole row deleted.

1234 should be deleted because the first instance of it has an "N" in Column C.

1245 should be deleted because the first instance of it has an "N" in Column C.

1356 should NOT be deleted because the first instance of it does not have an "N" in Column C.

1589 and 1679 should be deleted because the first instance of them have an "N" in Column C.

Note: Column B does not have to be "1" to be deleted, but rather the first number of the set.

 

[petertenthije]

Thank you , so just to be clear, if there is a "Y" as the first instance then it will not delete anything in that particular column A number?

That’s the plan.

For the first time I would recommend running the macro on a backup, just to be sure other data is not interfering. But yeah, this should work as advertised.

I’ll be going to bed now. Tomorrow morning I’ll check in again.

 

[DanteAmor]

Here another macro for you to consider:

Sub Delete_Rows()
  Dim lr As Long, i As Long, r As Range, n As Long
  lr = Range("AD" & Rows.Count).End(xlUp).Row
  Set r = Range("AD" & lr + 1)
  For i = 2 To lr
    n = WorksheetFunction.CountIf(Range("AD2:AD" & i), Range("AD" & i))
    If n = 1 And Range("AE" & i) = "N" Then Set r = Union(r, Range("AD" & i))
  Next i
  r.EntireRow.Delete
End Sub

 

[Lucasc10]

In addition to changing to AD/AE, I added the following bits of code:
1) A row will be added between rows 1 and 2. This is necessary to guarantee that cell AF2 mentions "delete".
2) The helper column (AF) will be removed when everything is done.
3) Auto filter will only be added if it does not already exist.

Sub Macro1()

' This formula is used to determine which cells should be deleted, and which should be kept.
    Range("AF1").FormulaR1C1 = "Helper column"
    Range("AF2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,R1C1:R[-1]C32,32,FALSE),IF(RC[-1]=""N"",""delete"",""do not delete""))"

' This part of the macro will flash-fill the formula in cell D2 into every row.
' This is based on the data in column A.
With Sheets("Sheet1")
.Range("AF2").AutoFill .Range("AF2:AF" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

' An autofilter is placed on column D
If Sheets("Sheet1").AutoFilterMode = True Then
'do nothing
Else
    Selection.AutoFilter
End If

' Column AF is filter on "delete"
    Worksheets("Sheet1").Range("AF1").AutoFilter Field:=32, Criteria1:="delete"

' This enters a dummy value "Delete" in column AF.
' This is done to make sure there is something to delete, but also to make sure
' that column AF will always begin with a "Delete". This is crucial for the next step.
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").FormulaR1C1 = "dummy"
    Range("AF2").FormulaR1C1 = "delete"

' The filtered data is selected, and deleted.
    Range("AF2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete

' The filter is removed
    ActiveSheet.ShowAllData

' The contents of the helper column is removed
    Columns("AF:AF").ClearContents

End Sub

Hello,

When I run the formula, it seems to work good but I run into one problem. I will share a piece of my actual spreadsheet, with unimportant columns hidden. In the highlighted, it shows "do not delete" in cell AF62. I would like it to delete this because the first instance was marked "N". This should mean that both rows 61 and 62 should be deleted as they are both "8598265". I would like it to see that one of them is being deleted and think "I need to delete the other one as well". I guess I want it to where if it meets the criteria to be deleted, all of the ones that share the same column A value would be deleted. Thank you so much for your help so far and please ask if you have any additional questions.

 

[petertenthije]

both rows 61 and 62 should be deleted as they are both "8598265".

Actually, there is a difference between row 61 and 62. You'll note that every row, except row 62, has a little green corner. These green corners indicate that the entered value is a text value. If there is no green arrow, like in row 62, the entry is numerical.

Because one value is numerical, and one value is text, the two rows are considered to not match each other.

Below updated VBA should have you covered.

Sub Macro1()

' This formula is used to determine which cells should be deleted, and which should be kept.
    Range("AF1").FormulaR1C1 = "Helper column"
    Range("AF2").FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,R1C1:R[-1]C32,32,FALSE)," & Chr(10) & "IFERROR(VLOOKUP(TEXT(RC1,""#""),R1C1:R[-1]C32,32,FALSE)," & Chr(10) & "IFERROR(VLOOKUP(NUMBERVALUE(RC1),R1C1:R[-1]C32,32,FALSE)," & Chr(10) & "IF(RC[-1]=""N"",""delete"",""do not delete""))))"

' This part of the macro will flash-fill the formula in cell D2 into every row.
' This is based on the data in column A.
With Sheets("Sheet1")
.Range("AF2").AutoFill .Range("AF2:AF" & .Cells(.Rows.Count, "A").End(xlUp).Row)
End With

' An autofilter is placed on column AF
' It first check if there is an autofilter, if there is it REMOVES the auto-filter.
' The existing auto-filter likely does not contain the worker column, therefor the
' auto-filter is added new.
If Sheets("Sheet1").AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
Else
    'do nothing
End If
    Range("A1:AF1").Select
    Selection.AutoFilter

' Column AF is filter on "delete"
    Worksheets("Sheet1").Range("AF1").AutoFilter Field:=32, Criteria1:="delete"

' This enters a dummy value "Delete" in column AF.
' This is done to make sure there is something to delete, but also to make sure
' that column AF will always begin with a "Delete". This is crucial for the next step.
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A2").FormulaR1C1 = "dummy"
    Range("AF2").FormulaR1C1 = "delete"

' The filtered data is selected, and deleted.
    Range("AF2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Delete

' The filter is removed
    ActiveSheet.ShowAllData

' The contents of the helper column is removed
    Columns("AF:AF").ClearContents

End Sub

 

[Fluff]

Cross posted Code or Formula to delete rows based on multiple criteria

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[DanteAmor]

I would like it to delete this because the first instance was marked "N". This should mean that both rows 61 and 62 should be deleted

Hello, here my corrected macro for tyou to consider. This macro includes columns A and AE, also to consider values as a number or as text.

Sub Delete_Rows()
  Dim lr As Long, i As Long, r As Range, n As Long, dic As Object
  lr = Range("A" & Rows.Count).End(xlUp).Row
  Set r = Range("A" & lr + 1)
  Set dic = CreateObject("Scripting.Dictionary")
  For i = 2 To lr
    n = WorksheetFunction.CountIf(Range("A2:A" & i), Range("A" & i))
    If n = 1 And Range("AE" & i) = "N" Then
      dic(Val(Range("A" & i).Value)) = Empty
      Set r = Union(r, Range("A" & i))
    End If
    If dic.exists(Val(Range("A" & i).Value)) Then
      Set r = Union(r, Range("A" & i))
    End If
  Next i
  r.EntireRow.Delete
End Sub

Please Note: put here the link to other publications with the same question.

 

[Lucasc10]

Code or Formula to delete rows based on multiple criteria

Hello all! I am looking for a code or formula to delete the entire row if it meets the following conditions... I would like the entire row deleted if "Column B" is the first instance (by column A) and it correlates to an "N" in "Column C". I would also like every row that contains the same...

www.excelforum.com

Above is the link in another publication. I'm sorry!

 

[DanteAmor]

Thanks for putting the link.
Did you try the code in post #16?

 

[Lucasc10]

This works great, Thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.