MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Unique column

Posted by Charisse on February 06, 2002 9:09 AM

I am trying to make it so that a column can only contain a unique number.
If anyone can assist I would GREATLY appreciate it!!

Posted by Damon Ostrander on February 06, 2002 1:30 PM

Hi Charisse,

I'm not sure if this is exactly what you want, but here is a macro that keeps anyone from entering duplicate data in any column on a worksheet. You CAN enter the same data in two different columns, but no duplicates in the same column. It only checks manually-entered data, not formula results. It works with text, dates, etc., as well as numbers.

Here's the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LastRow As Integer
If Not IsEmpty(Target.Value) Then
LastRow = Cells(32767, Target.Column).End(xlUp).Row
For i = 1 To LastRow
If i <> Target.Row Then
If Cells(i, Target.Column).Value = Target.Value Then
MsgBox Target.Value & " already exists.", vbExclamation
Target.Value = Empty
Exit For
End If
End If
Next i
End If
End Sub

This code must be placed in the worksheet's event code module. If you don't know how to do this, its easy. Just right-click on the worksheet's tab, select View Code..., then paste the code into the VBE code window that appears.

Happy computing.