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?

 

[Legacy 98055]

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...

<pre>
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

</pre>
Tom