¿Cómo comprobar si hay valores duplicados en Excel vba?

Question

Las validaciones de datos en Excel no funcionan cuando el usuario copia y pega los datos.

Quiero que aparezca un mensaje de error cuando el usuario pegue datos duplicados en una columna.

Estoy usando el siguiente código (otra alternativa) pero esto no es lo que quiero. Quiero que esto se verifique cuando el usuario inserte los datos y, si hay algún problema, arrojar un mensaje de error.

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

Answer 1

Al usar el Changeevento, podemos atrapar entradas duplicadas sin validación de datos:

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

NOTA:

1. el código puede manejar ambas entradas escritas en la columnaAasí como copiar/pegar en la columna
2. el código puede manejar copiar/pegar varias celdas en la columna