Avoid duplicate data entry in the range of column "E"

Hi.

I need to avoid duplicate data entry in the range of column "E".

by entering a value repeated the same should be automatically deleted.

 
Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range
Set r1 = Range("E2:E100")

If r1 = 0 Then

Else

If r1 > 0 Then MsgBox "The new entry is repeated according to the lines: " & r1

End If
End Sub

Hi

You can also do it, using in Custom Validation rules, this formula.

=COUNTIF(E:E,E1)=1

I need via VBA, can you do?
then I will adapt into a form

thank you!!!

Ηι

You are welcome.

NO. I can't. I am not familiar with VBA. Sorry.

OK, thank you!!

Hi..

Any idea?

thank you!

Hi,

Here's a bit of code that may help you.

Sub Test_Match()
For k = 100 To 2 Step -1

If Application.WorksheetFunction.CountIf(Range("E" & k), "sam") = 1 Then
    Cells(1, k).EntireRow.Delete '''
End If

Next k

End Sub

Tanking off for the evening. Good luck.

I tested your code has something wrong, should be the criterion "sam". anyway I need to be based ..

Sub Worksheet_Change (ByVal Target As Range)

Hi Marreco,

See if this does the trick:

Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer 'Range ("E2:E100")
If Target.Column = 5 Then
For i = 2 To Target.row
If Cells(i, 5) = Target Then
Cells(Target.row, 5).EntireRow.Delete Shift:=xlUp
MsgBox "The new entry is repeated according to the lines: " & i
Exit For:: End If:  Next i: End If: End Sub

Hi.

Very good!!

Thank you!!!

Hi Marreco,

You're welcome! Maybe you ought to change:

For i = 2 To Target.row

to

For i = 2 To Target.row-1