Remove duplicates

[tiredofit]

I have this code in Sheet1 to remove duplicates:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Application.CountIf(Me.Columns(1), Target.Value) <> 1 Then
    
        Application.EnableEvents = False
        
        Application.Undo
        
        Application.EnableEvents = True
    
    End If
    
End Sub

If I type 1 into cell A1, nothing happens, as expected.

If I then type 1 into cell A2, the code reverses and cell A2 becomes blank, also as expected.

Suppose instead of the value 1 but I type the value 1@ into cell A1. Again nothing happens.

But if I now type 1@ into cell A2, the code DOES NOT reverse that value, even though it's already in cell A1.

Is there something special with @ symbols?

 

[bobsan42]

in this case there is a thing is special about @ - it is normally recognised as a part of an email address.
Your code works, but Excel doesn't wait for anyone
The moment you type 1@ and press Enter Excel converts it to a hyperlink: mailto:1@
When you execute Application.Undo it actually undoes the conversion to Hyperlink.
The problem with Application.Undo is that it only works for one action. So if you execute it a second time it will undo the previous undo and you will end up with a 1@ hyperlink.

 

[tiredofit]

Thanks for the explanation.