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.

 

[Lucasc10]

Also, Column B and C are Column AD and AE in my actual workbook, respectively

 

[Michael M]

Is the data sorted by Column "AD" ??

 

[Lucasc10]

Is the data sorted by Column "AD" ??

Yes, it is sorted by Column "A", then by column "AD"

 

[Michael M]

I'm a little confused, but does this do what you want...based on your picture of columns A,B, & C

Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
    If Cells(r, 1).Value <> Cells(r - 1, 1).Value And Cells(r, 3).Value = "N" Then
        Rows(r).Delete
    End If
Next r
End Sub

 

[Lucasc10]

This doesn't work. Not sure if it is because my real workbook has columns AD and AE isntead of B and C or not.

Essentially I want it to find the first reference of any number of column A, and see if there is an "N" in column AE. (B in the example.) If there is one, I want it to delete all of the instances of that particular number in Column A.

 

[petertenthije]

Could you try this? It will use column D as a helper column!

Sub Macro1()
 
' This formula is used to determine which cells should be deleted, and which should be kept.
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,R1C1:R[-1]C4,4,FALSE),IF(RC[-1]=""N"",""delete"",""do not delete""))"

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

' An autofilter is placed on column D
    Selection.AutoFilter
    Worksheets("Sheet1").Range("D1").AutoFilter Field:=4, Criteria1:="delete"

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

' The filter is removed
    ActiveSheet.ShowAllData
End Sub

 

[Lucasc10]

Could you try this? It will use column D as a helper column!

Sub Macro1()

' This formula is used to determine which cells should be deleted, and which should be kept.
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=IFERROR(VLOOKUP(RC1,R1C1:R[-1]C4,4,FALSE),IF(RC[-1]=""N"",""delete"",""do not delete""))"

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

' An autofilter is placed on column D
    Selection.AutoFilter
    Worksheets("Sheet1").Range("D1").AutoFilter Field:=4, Criteria1:="delete"

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

' The filter is removed
    ActiveSheet.ShowAllData
End Sub

Thank you! Could you help me if Column B was actually AD and Column C was actually AE?

 

[petertenthije]

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

 

[Lucasc10]

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?