Delete if Three Matches in Three Columns

[ryguy7272]

I am trying to loop through a data set (sorted by ColumnD, ColumnF, and
ColumnG), and trying to delete dupes in ColumnD, ColumnF, and ColumnG.


'Compare and delete if dupes
Dim rCell As Range
With ActiveSheet
For Each rCell In .Range("D1" & _
.Range("D" & .Rows.Count).End(xlUp).Row)
With rCell
If .Value = .Offset(0, 2).Value Then
If .Value = .Offset(0, 3).Value Then
rCell.EntireRow.Delete
Else
End If
End If
End With
Next rCell
End With

The dupes aren’t deleted and I know I have to delete from the bottom up…but
I don’t think my code is doing that now. How can I change this to work
correctly?

Thanks,
Ryan---

 

[Don Guillett]

try this
Sub deleltedupcolumns()
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If Cells(i, "e") = Cells(i, "d") And Cells(i, "f") = Cells(i, "d") Then
Rows(i).Delete
Next i
End Sub

 

[ryguy7272]

Well, thanks Don! That looks pretty sexy, but it didn't work as it was. I
made a few minor changes, and still no luck. Working with this now:

For i = Cells(Rows.Count, 4).End(xlUp).Row To 2 Step -1
If Cells(i, 6) And Cells(i, 7) = Cells(i, 4) Then
Rows(i).Delete
End If
Next i

I get a Run-time Error 13; type mismatch.
Any ideas?

Thanks,
Ryan---

 

[Don Guillett]

Worked for me, AS TESTED, except for the email word wrap on the THEN. Excel
wants it this way
If Cells(i, "e") = Cells(i, "d") And Cells(i, "f") = Cells(i, "d") Then
or
If Cells(i, 6 = Cells(i, 4 )and Cells(i, 7) = Cells(i, 4) Then

Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[ryguy7272]

My data was a little screwed up before; didn't notice it. You were right and
I was wrong. Thanks so much!! Very helpful!!

Regards,
Ryan---