If there is a duplicate in column A delete that row

[djfscouse]

Hi,

Column A is mostly unique characters, but sometimes the characters can repeat up to 7 times in that column. I need a script that will keep just one unique character and its data in that row and delete any rows where it appears again.

I have tried a few different scripts in the forum that were close to what I wanted but sadly they did not work.

Thanks for any help.

David

[mike7952]

Try this. Change $A$1:$E$ to your range

Sub Macro1()
  With ActiveSheet
    .Range("$A$1:$E$" & .Cells(Rows.Count, "a").End(xlUp).Row).RemoveDuplicates Columns:=1, Header:=xlYes
  End With
End Sub

[djfscouse]

The above works perfectly for 2007, is there a 2003 version?

[mike7952]

Yea short and sweet, but only works in 2007 or greater. Try this for 2003

Sub abc()
Dim Ptr As Long
Dim RangeToDelete As Range
Dim Lastrow As Long


    Lastrow = Cells(Rows.Count, "a").End(xlUp).Row
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Ptr = 2 To Lastrow
            If Not .Exists(Cells(Ptr, "a").Value) Then
                .Item(Cells(Ptr, "a").Value) = Cells(Ptr, "a").Value
            Else
                If RangeToDelete Is Nothing Then
                    Set RangeToDelete = Cells(Ptr, "a")
                Else
                    Set RangeToDelete = Union(RangeToDelete, Cells(Ptr, "a"))
                End If
            End If
        Next
        a = .items
    End With
    If Not RangeToDelete Is Nothing Then
        RangeToDelete.EntireRow.Delete
    End If
End Sub

[djfscouse]

Perfect, Thanks again Mike!