Delete Rows based on 2 conditions

[mrsushi]

Trying to figure out how to tweak the below VBA code to delete duplicates. The current code deletes data which is duplicated in column B.
However, I need certain conditions met ie value in column B(Security) is duplicate, then look at Column D(Lad) and delete "DFL (if its in the column)

Any ideas please?
thanks
.

Option Explicit
Sub DeleteDuplicates1()

Dim x As Long
Dim LastRow As Long

LastRow = Range("B65536").End(xlUp).Row
For x = LastRow To 5 Step -1
If Application.WorksheetFunction.CountIf(Range("B5:B" & x), Range("B" & x).Text) > 1 Then
Range("B" & x).EntireRow.DELETE
End If
Next x

End Sub

Time	Security	Description	Lad
Close	2001119	AAR CORP COM USD1	DFL
Close	2001119	AAR CORP COM USD1	BID
Close	2037062	HELIX ENERGY SOLUTIONS GROUP	DFL
Close	2037062	HELIX ENERGY SOLUTIONS GROUP	BID
Close	2108601	BOEING CO,CMN US$5	DFL
Close	2108601	BOEING CO,CMN US$5	BID
Close	2109723	BOMBARDIER INC 'B' SUB VTG NPV	DFL
Close	2109723	BOMBARDIER INC 'B' SUB VTG NPV	BID
Close	2130109	ONEOK INC COM NPV	DFL
Close	2130109	ONEOK INC COM NPV	BID
Close	2182531	CORE LABORATORIES N.V. EUR0.04	DFL
Close	2182531	CORE LABORATORIES N.V. EUR0.04	BID
Close	2186072	CALLON PETROLEUM CO USD0.01	DFL
Close	2186072	CALLON PETROLEUM CO USD0.01	BID
Close	2186254	CENTURY ALUMINUM COMPANY	DFL
Close	2186254	CENTURY ALUMINUM COMPANY	BID
Close	2387109	GUESS COM US$0.01	DFL
Close	2387109	GUESS COM US$0.01	BID
Close	2405302	HALLIBURTON CO COM US$2.50	DFL
Close	2405302	HALLIBURTON CO COM US$2.50	BID
Close	2408044	HAWAIIAN HOLDINGS INC	DFL
Close	2408044	HAWAIIAN HOLDINGS INC	BID

 

[Yongle]

One way...
(test on a COPY of your data)

Sub DeleteDuplicates1()
    Dim x As Long, LastRow As Long, DFL As Boolean, Dup As Boolean
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    For x = LastRow To 5 Step -1
        DFL = (Cells(x, "D") = "DFL")
        Dup = (WorksheetFunction.CountIf(Range("B5:B" & LastRow), Cells(x, "B")) > 1)
        If Dup And DFL Then Range("B" & x).EntireRow.Delete
    Next x
End Sub

Remember to use code tags when posting code to make it easier to read
Click on <vba/> icon and paste code between the tags
[ CODE=vba ] PASTE CODE HERE [ /CODE ]

 

[mrsushi]

One way...
(test on a COPY of your data)

Sub DeleteDuplicates1()
    Dim x As Long, LastRow As Long, DFL As Boolean, Dup As Boolean
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    For x = LastRow To 5 Step -1
        DFL = (Cells(x, "D") = "DFL")
        Dup = (WorksheetFunction.CountIf(Range("B5:B" & LastRow), Cells(x, "B")) > 1)
        If Dup And DFL Then Range("B" & x).EntireRow.Delete
    Next x
End Sub

Remember to use code tags when posting code to make it easier to read
Click on <vba/> icon and paste code between the tags
[ CODE=vba ] PASTE CODE HERE [ /CODE ]

That's worked perfect. Thanks Yongle

 

[Yongle]

thanks for your feedback

This will run quicker if there is a lot of data

Sub DeleteDuplicates2()
    Dim x As Long, LastRow As Long, DFL As Boolean, Dup As Boolean, Rng As Range
    LastRow = Range("B" & Rows.Count).End(xlUp).Row
    Set Rng = Range("B" & LastRow + 1)
    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
            For x = LastRow To 5 Step -1
                DFL = (Cells(x, "D") = "DFL")
                Dup = (WorksheetFunction.CountIf(Range("B5:B" & LastRow), Cells(x, "B")) > 1)
                If Dup And DFL Then Set Rng = Union(Rng, Range("B" & x))
            Next x
            Rng.EntireRow.Delete
        .Calculation = xlCalculationAutomatic
    End With
End Sub