VBA检查出EXCEL某列单元格数据的唯一性

将sheet1 A列中有重复的单元格数据显示在sheet2的A列中
如：
在sheet1中
A列
A100
B-20
C200
A100
D
E55
B-20

在sheet2中
A列
A100
B-20

方法一

假设你的原数据在sheet1的A列的前10个，可以这样：

Sub 筛选重复数据()
Application.ScreenUpdating = False
Sheets("Sheet1").Select
Range("A2:A10").Select
Selection.FormatConditions.AddUniqueValues
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).DupeUnique = xlDuplicate
With Selection.FormatConditions(1).Font
.Color = -16383844
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("A1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$A$10").AutoFilter Field:=1, Criteria1:=RGB(156, 0 _
, 6), Operator:=xlFilterFontColor
Range("A2:A10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
ActiveSheet.Paste

End Sub
-----------------------------------------------------------------------------
方法二
Sub 筛选重复数据()
Application.ScreenUpdating = False

Dim m As Integer
Dim n As Integer
m = Application.WorksheetFunction.Count(Sheets("Sheet1").Range("A2:A65536"))
For n = 2 To m

If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("A2:A65536"), Sheets("Sheet1").Cells(n, 1)) >= 2 Then
Sheets("Sheet1").Select
Sheets("Sheet1").Cells(n, 1).Select
Selection.Copy
Sheets("Sheet2").Select
Range("OFFSET($A$1,1+COUNTA($A$1:$A$65530),)").Select
Selection.PasteSpecial Paste:=xlPasteValues
Else

End If
Next n

End Sub

温馨提示：内容为网友见解，仅供参考

当前网址：https://22.t2y.org/zz/2fhhifti6.html