Prevent duplicate but for one cell only

[suny100]

here is code for prevent duplicates in sheet upon coulmn 1 but it works just if iam pasting one cell only but if i paste more than 1 it not work , could anybody help me?

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Finish
If Target.Column = 1 Then
For Each cell In Selection
If Application.WorksheetFunction.CountIf(Columns(1), cell.Value) > 1 Then
Application.Undo
MsgBox "You were trying to paste in duplicate entries"
End If
Next cell
End If
Finish:
Application.EnableEvents = True
End Sub

[Chabu]

replace "selection" with "Target" like this and it works fine
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
On Error GoTo Finish
If Target.Column = 1 Then
For Each aCell In Target
If Application.WorksheetFunction.CountIf(Columns(1), aCell.Value) > 1 Then
Application.Undo
MsgBox "You were trying to paste in duplicate entries"
End If
Next aCell
End If
Finish:
Application.EnableEvents = True
End Sub


Greetings

[suny100]

replace "selection" with "Target" like this and it works fine
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
On Error GoTo Finish
If Target.Column = 1 Then
For Each aCell In Target
If Application.WorksheetFunction.CountIf(Columns(1), aCell.Value) > 1 Then
Application.Undo
MsgBox "You were trying to paste in duplicate entries"
End If
Next aCell
End If
Finish:
Application.EnableEvents = True
End Sub


Greetings


Thanks for your help ,but it not work fine as it prevent all data even not duplicate (ie. if iam trying to paste 6 cells contain 1 duplicate and 5 unique, it prevent all of them not just the 1 that duplicated)

[mikerickson]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
On Error Goto Finish
Application.EnableEvents = False
If Target.Column = 1 Then
For Each aCell In Target
If Application.WorksheetFunction.CountIf(Columns(1), aCell.Value) > 1 Then
aCell.ClearContents
End If
Next aCell
End If
Finish:
Application.EnableEvents = True
End Sub

[suny100]

mikerickson, thanks for your reply but it also not work it clear all of them also not duplicated values only

[CharlesH]

Perhaps a copy of your workbook so we can see what your working with?