How to check for duplicate value in Excel vba?

Question

Data validations in Excel are not working when user copy-pastes the data.
I want an error message to be thrown when the user pastes duplicate data in a column.
I'm using the following code(another alternative) but this is not what I want. I want this to be checked when the user inserts the data and if there is any issue then throw an error message.
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) 
  Dim rngCell As Range Dim lngLstRow As Long lngLstRow = Sheet1.UsedRange.Rows.Count
  For Each rngCell In Sheet1.Range("A1:A" & lngLstRow) 
    If CountIf(Range("A:A"),A1) > 2 Then
      MsgBox "Please enter unique value " & rngCell.Address
        rngCell.Select
    End If 
  Next 
End Sub

Gary's Student · Answer

By using the Change event, we can trap duplicate entries without data validation:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim RangeOfInterest As Range, Intersection As Range, cell As Range
    
    Set RangeOfInterest = Range("A:A")
    Set Intersection = Intersect(RangeOfInterest, Target)
    If Intersection Is Nothing Then Exit Sub
    
    With Application
        For Each cell In Intersection
            If .WorksheetFunction.CountIf(RangeOfInterest, cell.Value) > 1 Then
                .EnableEvents = False
                    .Undo
                .EnableEvents = True
                MsgBox "duplicates not allowed"
            Exit Sub
            End If
        Next cell
    End With
End Sub

NOTE:

the code can handle both typed entries into column A as well as copy/pastes into the column
the code can handle multi-cell copy/pastes into the column

How to check for duplicate value in Excel vba?

One Answer

Add your own answers!

Ask a Question