Prevent entering duplicate cell data in a column

  • Thread starter Thread starter Samantha James
  • Start date Start date
S

Samantha James

Hi,

I am struggling to find a method prevent users entering
duplicate data to a coulumn.

The requirement is quite simple -- check
if the same cell value is already found in the column to
which data is currently being entered.

For instance, when entering data to cell A8, I need
to check if the the same value is already found in the
cells ranging from A2 to A20.

This is just to prevent users entering dupliate data.

Could someone kindly give me any sample code or hint ?

Thank you very much
Samantha
 
Samantha,

Highlight A2:A20
From Data | Validation:
Allow = Custom
Formula: =COUNTIF($A$2:$A$20,A2)=1


Rob
 
Hi
Put this into the code module behind the sheet where you are inputting
the data
The IsEmpty check and the "On Error Resume Next" parts of the code are
to deal with the very first cell.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim ColumnVariant As Variant
Dim i As Long, Counter As Integer
Dim RangeSize As Long
Dim TestValue As Variant

ColumnVariant = Range(Range("A1"), Range("A65536").End(xlUp)).Value
If IsEmpty(ColumnVariant) Then Exit Sub
TestValue = Target.Value
On Error Resume Next
RangeSize = UBound(ColumnVariant, 1)
On Error GoTo 0
If RangeSize = 0 Then Exit Sub
For i = 1 To RangeSize
If TestValue <> "" Then
If ColumnVariant(i, 1) = TestValue Then Counter = Counter + 1
End If
If Counter = 2 Then
MsgBox "Value already exists in Column A"
Target.Clear
Target.Activate
Exit Sub
End If
Next i
End Sub


regards
Paul
 
Back
Top