Delete double name

[MJA]

Hello Excel lovers,

I have a column (column A) with names.
When I add a name to the column, I play a macro that deletes the name if the name is already in the column!

But my solution doesn’t work.
When I play the macro it always deletes the last name!

Why?!
I think it goes wrong with the end(xldown) part.
When I play the macro step by step (with F8) the value of end(xldown) = 4121?!?!?!

Sub Test()
i = 1
Do Until Cells(i, 1).Value = ""
If Cells(1, 1).End(xlDown).Value = Cells(i, 1).Value Then
MsgBox "This name already exists!"
Cells(1, 1).End(xlDown).EntireRow.Delete
End If
i = i + 1
Loop
End Sub

Who can help me?

[Tom Schreiner]

Hi
I'm assuming that you are entering the new name on the next available row?
The value is finding it'self as a match and then deleting it'self.
Try this...

Sub Test()

Dim c, LastRow

With ActiveSheet

    LastRow = .UsedRange.Columns(1).Rows.Count

    For Each c In .Range("a2:A" & LastRow)

        If .Cells(LastRow, 1) = c.Value Then

            If c.Row = LastRow Then Exit Sub

            MsgBox "This name already exists!"

            .Rows(LastRow).Delete

        End If

    Next

End With

End Sub

Tom