Stop Duplicate Entries if Hand Typed or Pasted COUNTIF does nor work for Paste

[daleholden]

Hi Guys

I have a pre-filled column up to about 100 of SKU numbers. I have checked there are no duplicates currently. I have tried adding the COUNTIF function to stop duplicates but this only works for hand typed entries. I need a solution to stop duplicates hand typed or pasted in please can somebody help me?

Dale

 

[mikerickson]

A routine like this in the sheet's code module will do that, you might want to eliminate the UnDo line if you prefer that style.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim fStr As String
    With Me.Range("A:A")
        If Not Application.Intersect(Target, .Cells) Is Nothing Then
        fStr = "=Sumproduct(COUNTIF(" & .Address & "," & Application.Intersect(Target, .Cells).Address & "))"

        If Application.Intersect(Target, .Cells).Cells.Count <> Evaluate(fStr) Then
            MsgBox "Duplicate detected"
            With Application
                .EnableEvents = False
                .Undo
                .EnableEvents = True
            End With
        End If
        End If
    End With
End Sub

 

[daleholden]

Hi Mike
Bit of a newbie with VBA but i have added it to to the workbook tab but it still allows me to paste in ???

 

[mikerickson]

You put it in a normal module.

What you need to do is double click on you sheet from the list in the window on the left. That should open a widow that says something like "Workbook1 - Sheet1 (code)" at the top.

Copy paste the code into that.

 

[daleholden]

HI Mike


I have tried that but still will not work when i paste into the cell?????

I am getting 2 errors when i tab from my last entry in table to add a new entry it opens in the next free A cell i get the duplicate error (with noting in cell)

I say OK to warning box it then allows me to paste or type in duplicate!!!!

 

[mikerickson]

replace the .Undo line with a message box and see where that leads you.

 

[daleholden]

HI Mike

Still no good

See video here


Dale