excel - 检查excel中的唯一值

Question

如果数据不是唯一的，我想防止在 excel 中复制粘贴数据。我使用以下宏。

Private Sub Worksheet_Change(ByVal Target As Range) 
Dim AffectedCells As Range
Dim Cell As Range

Set AffectedCells = Intersect(Target, Target.Parent.Range("A:A"))
If Not AffectedCells Is Nothing Then 
  For Each Cell In AffectedCells
    If CountIf(Range("A:A"),A1) >2 Then
      MsgBox "The data """ & Cell.Value & """ inserted in " & Cell.Address & " in column A is duplicste. We undo!", vbCritical Application.Undo 'undo insert     
      Exit Sub 
    End If 
  Next Cell 
End If
End Sub

它仍然允许粘贴数据。

如果值重复，我尝试让 excel 不保存自身，但它仍在保存。

    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

标签： excelvbaexcel-formula