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

Badge Type: Badge Name	Is a Young Lawyer Member	Customer Number	Full Name	Firm Name
test 1	0	391026	first name1	last name 1
test 2	1	118004	first name 2	last name 2
test 3	0	64096	first name3	last name 3
test 4	0	500461	first name4	last name 4
test 5	0	500461	first name 4	last name 4

<colgroup><col width="152"><col width="80"><col width="71" span="3"></colgroup><tbody>
</tbody>

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