Prevent Duplicate Entries

**whousedmy** · 05-08-2009, 01:22 AM

Hi All,

New here, I wanna do a "Prevent Duplicate Entries" into my column i try the following:

They all work well but if I'm using copy and paste It doesn't work! why is tat so?

Second issue will be i'm wanna do prevent duplication for the whole column not just one cell. Anyone can help with this 2 problem??

Thanks alot in adv and for even reading!

**royUK** · 05-08-2009, 02:28 AM

Copy & pasting overwrites the Data validation

**whousedmy** · 05-08-2009, 03:38 AM

Originally Posted by royUK

Copy & pasting overwrites the Data validation

Thanks alot for your reply. So is there any other way or matter to get what I need?

**whousedmy** · 05-13-2009, 09:40 PM

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
Dim ans As String
Const myCol As Long = 3
If Intersect(Target, Columns(myCol)) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Intersect(Target, Columns(myCol))
If Application.CountIf(Columns(myCol), r.Value) > 1 Then
MsgBox (r.Value & " already exsists")
r.ClearContents
End If
Next
Application.EnableEvents = True
End Sub

Here the correct coding to solve duplicate entries in the same columns.

**slash4u** · 08-17-2010, 08:18 AM

can u tell in detail how to do in excel 2003 , i mean where to enter the coding and everything...pleaseeeee i have the exact problem so please help me too

**royUK** · 08-17-2010, 11:40 AM

Copy the Excel VBA code
Select the workbook in which you want to use the code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
In the Project Explorer, find your workbook, and open the list of Microsoft Excel Objects
Right-click on the ThisWorkbook object, and choose View Code
Where the cursor is flashing, choose Edit | Paste