CODEWITHSUNDEEP
vbaexcel
aj_bk
aj_bk

Reputation: 194

Deleting entire duplicate rows with conditions and

I have data with duplicates (column "c") and I want to delete the row that has numbers in column "D". but only for the duplicates with odd dates, as seen in the picture enter image description here

this is the code im using, but i dont know how to delete the row with data in "D" and is a duplicate

 Sub del_doops()
    Dim RowNdx As Long
    Dim RowNdx2 As Long

    For RowNdx = Range("A1:f1").End(xlDown).Row To 2 Step -1
        For RowNdx2 = RowNdx - 1 To 1 Step -1   'Begin at one above RowNdx

            If Cells(RowNdx, "b").Value = Cells(RowNdx2, "b").Value And _
                Cells(RowNdx, "C").Value = Cells(RowNdx2, "C").Value And _
                Cells(RowNdx, "E").Value = Cells(RowNdx2, "E").Value And _
                Cells(RowNdx, "F").Value <> Cells(RowNdx2, "F").Value Then
                Rows(RowNdx2).Delete 'this is where i need help
            End If

        Next RowNdx2
    Next RowNdx

End Sub

Upvotes: 5

Views: 115

Answers (2)

Jacob Ukken
Jacob Ukken

Reputation: 46

Sub del_doops()
Dim RowNdx As Long
Dim RowNdx2 As Long
For RowNdx = Range("A1:f1").End(xlDown).Row To 2 Step -1
    For RowNdx2 = RowNdx - 1 To 1 Step -1   'Begin at one above RowNdx
        If Cells(RowNdx, "B").Value = Cells(RowNdx2, "B").Value And _
        Cells(RowNdx, "C").Value = Cells(RowNdx2, "C").Value And _
        Cells(RowNdx, "E").Value = Cells(RowNdx2, "E").Value And _
        Cells(RowNdx, "F").Value = Cells(RowNdx2, "F").Value Then
            If Cells(RowNdx, "D").Value = vbNullString And _
            Cells(RowNdx2, "D").Value <> vbNullString Then
                Rows(RowNdx2).Delete
            Else
                Rows(RowNdx).Delete
            End If
        End If
    Next RowNdx2
Next RowNdx
End Sub

Upvotes: 2

R3uK
R3uK

Reputation: 14547

Change Sheet1 to the name of your sheet in Set wS = ThisWorkbook.Sheets("Sheet1") :

Sub del_doops()
Dim RowNdx As Long
Dim RowNdx2 As Long
Dim wS As Worksheet

Set wS = ThisWorkbook.Sheets("Sheet1")
With wS
    For RowNdx = .Range("A" & .Rows.Count).End(xlUp).Row To 2 Step -1
        For RowNdx2 = RowNdx - 1 To 1 Step -1   'Begin at one above RowNdx
            If .Cells(RowNdx, "B").Value = .Cells(RowNdx2, "B").Value And _
                    .Cells(RowNdx, "C").Value = .Cells(RowNdx2, "C").Value And _
                    .Cells(RowNdx, "E").Value = .Cells(RowNdx2, "E").Value And _
                    .Cells(RowNdx, "F").Value <> .Cells(RowNdx2, "F").Value Then
                If .Cells(RowNdx, "D").Value <> vbNullString Then
                    .Rows(RowNdx).Delete
                Else
                    If .Cells(RowNdx2, "D").Value = vbNullString Then .Rows(RowNdx2).Delete
                End If
            End If
        Next RowNdx2
    Next RowNdx
End With 'wS
End Sub

Upvotes: 2

Related Questions