Delete rows based upon duplicate Data

[saribobari2002]

I have a very large spreadsheet that has several rows of data that are duplicates of other rows, except for data in one cell.

For example,
A B C D E
[TABLE="class: cke_show_border, width: 445"]
<colgroup><col width="152"><col width="80"><col width="71" span="3"></colgroup><tbody>[TR]
[TD="width: 152"]Badge Type: Badge Name[/TD]
[TD="width: 80"]Is a Young Lawyer Member[/TD]
[TD="width: 71"]Customer Number[/TD]
[TD="width: 71"]Full Name[/TD]
[TD="width: 71"]Firm Name[/TD]
[/TR]
[TR]
[TD]test 1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]391026[/TD]
[TD] first name1[/TD]
[TD]last name 1[/TD]
[/TR]
[TR]
[TD]test 2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]118004[/TD]
[TD]first name 2[/TD]
[TD]last name 2[/TD]
[/TR]
[TR]
[TD]test 3[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]64096[/TD]
[TD]first name3[/TD]
[TD]last name 3[/TD]
[/TR]
[TR]
[TD]test 4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500461[/TD]
[TD]first name4[/TD]
[TD]last name 4[/TD]
[/TR]
[TR]
[TD]test 5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]500461[/TD]
[TD]first name 4[/TD]
[TD]last name 4[/TD]
[/TR]
</tbody>[/TABLE]


I would like a formula or macro that will find the match C4 & C5 and then delete the entire row C5.

There are many instances where there are between 3-6 additional rows that have this type of duplicated data.

Thank for any help or suggestions.

​​​​​​​Eric

 

[JLGWhiz]

give this a shot

Sub t()
Dim i As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1
            If Application.CountIf(Range("C:C"), .Cells(i, 3).Value) > 1 Then
                Rows(i).Delete
            End If
        Next
    End With
End Sub

Be sure that you want to delete an entire row based on duplicates in only one column.

 

[saribobari2002]

Worked perfectly.

Thanks vm

give this a shot

Sub t()
Dim i As Long
    With ActiveSheet
        For i = .Cells(Rows.Count, 3).End(xlUp).Row To 2 Step -1
            If Application.CountIf(Range("C:C"), .Cells(i, 3).Value) > 1 Then
                Rows(i).Delete
            End If
        Next
    End With
End Sub

Be sure that you want to delete an entire row based on duplicates in only one column.

 

[JLGWhiz]

Worked perfectly.

Thanks vm

You're Welcome.
regards, JLG

 

[footoo]

Another way :

ActiveSheet.Range("A1:XFD" & Cells(Rows.Count, "C").End(3).Row).RemoveDuplicates Columns:=3, Header:=xlYes