vba 建议宏在 SINGLE 列中查找重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9469438/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
suggestions for a macro to find duplicates in a SINGLE column
提问by franklin
found a lot of questions involving finding duplicates in two columns :
发现了很多涉及在两列中查找重复项的问题：
i.e. MS Excel how to create a macro to find duplicates and highlight them?and excel mark duplicates values
即 MS Excel 如何创建一个宏来查找重复项并突出显示它们？和excel 标记重复值
However I'm trying to adapt code to be used to find duplicates in one column. For example here is a data set:
但是，我正在尝试调整代码以用于查找一列中的重复项。例如这里是一个数据集：
Column 1
第 1 栏
Foo
Bar
23
23
12
foo
Bar
bart
富
律师
23
23
12
富
律师
巴特
This is what I'm using right now:
这就是我现在正在使用的：
Function warnDupes()
Dim lastRow As Long
Dim dict As Object
' Let Col be the column which warnDupes operates on.
Dim Col As String
Col = "A"
Set dict = CreateObject("scripting.dictionary")
lastRow = range(Col & Rows.Count).End(xlUp).Row
On Error Resume Next
For i = lastRow To 1 Step -1
If dict.Exists(range(Col & i).value) = True Then
'range("Y" & i).EntireRow.Delete
MsgBox ("Hmm...Seems to be a duplicate of " & range(Col & i).value & _
" in Cell " & Col & i)
End If
dict.Add range(Col & i).value, 1
Next
End Function
So far I've got some code that does 90% of the job. 23 and 23 are matched. Bar and Bar are matched. etc. So the code matches both Strings and Ints. But I'd like the macro to be able to match Foo and foo as a duplicate as well. How do I make Excel ignore case?
到目前为止，我有一些代码可以完成 90% 的工作。23 和 23 是匹配的。Bar 和 Bar 匹配。等等。所以代码匹配字符串和整数。但我希望宏也能够将 Foo 和 foo 匹配为重复项。如何让 Excel 忽略大小写？
This question ( Function for detecting duplicates in Excel sheet) seems relevent but I'm having trouble adapting the code or understanding what the author did. Any improvements to the code, explanations or suggestions would be very much appreciated.
这个问题（用于检测 Excel 工作表中重复项的函数）似乎很相关，但我在调整代码或理解作者做了什么时遇到了麻烦。对代码、解释或建议的任何改进将不胜感激。
Thanks.
谢谢。
UPDATE:
更新：
Just noticed something really weird.
刚刚注意到一些非常奇怪的事情。
The data:
数据：
IB6061
IC6071
IB6061
IC6071
are matched whether I use my Macro or if I use the Conditional Formatting tool in Excel. Any reason why?
无论我使用宏还是使用 Excel 中的条件格式工具，都匹配。有什么理由吗？
采纳答案by markblandford
On your Exists() & .Add() lines, make both values the same case:
在 Exists() 和 .Add() 行上，使两个值相同：
If dict.Exists(UCase$(Range(Col & i).Value)) Then
and
和
dict.Add UCase$(Range(Col & i).Value), 1
dict.Add UCase$(Range(Col & i).Value), 1
That way the duplicates will always be added to the dictionary in uppercase and so case will never matter.
这样重复项将始终以大写形式添加到字典中，因此大小写无关紧要。
回答by Siddharth Rout
franklin
富兰克林
Why not an Excel formula?
为什么不是 Excel 公式？
If the values are in Col A then type this in Cell B1 and copy it down?
如果值在 Col A 中，则在单元格 B1 中键入它并将其复制下来？
=IF(COUNTIF(A:A,A1)>1,"It is a duplicate","It is not a duplicate")
It will also work for cases like "Foo" and "foo"
它也适用于像“Foo”和“foo”这样的情况
You can then also use Conditional Formatting using the above formula to highlight duplicates?
然后您还可以使用上述公式使用条件格式来突出显示重复项？
FOLLOWUP
跟进
The data:
IB6061
IC6071
are matched whether I use my Macro or if I use the Conditional Formatting tool in Excel.
Any reason why?
数据：
IB6061
IC6071
无论我使用宏还是使用 Excel 中的条件格式工具，都匹配。
有什么理由吗？
What formula are you using?
你用的是什么公式？
This works for me. Highlight Col A and then use this formula
这对我有用。突出显示 Col A，然后使用此公式
=COUNTIF(A:A,A1)>1
See snapshot
查看快照
Sid
锡德
回答by assylias
You could put all the keys in lower case, for example:
你可以把所有的键都用小写，例如：
Dim myKey as String
For i = lastRow To 1 Step -1
myKey = UCase(range(Col & i).value)
If dict.Exists(myKey) = True Then
'range("Y" & i).EntireRow.Delete
MsgBox ("Hmm...Seems to be a duplicate of " & range(Col & i).value & _
" in Cell " & Col & i)
Else
dict.Add myKey, 1
End If
Next i
回答by rkmax
This Works for me
这对我有用
Excel 2007
Excel 2007
Sub removeDuplicate(rg As Range, col as Integer)
rg.RemoveDuplicates Columns:=col, Header:=xlYes
End Sub
Excel 2003
Excel 2003
' Excel 2003
Option Explicit
Sub DeleteDups(range as String)
Dim x As Long
Dim LastRow As Long
' Range "A65536"
LastRow = Range(range).End(xlUp).Row
For x = LastRow To 1 Step -1
If Application.WorksheetFunction.CountIf(Range("A1:A" & x), Range("A" & x).Text) > 1 Then
Range("A" & x).EntireRow.Delete
End If
Next x
End Sub
回答by Jerry Beaucaire
You can add
你可以加
Option Compare Text
To the VERY TOP of the module, all the code in that module will now compare text non-case-sensitively.
对于模块的 VERY TOP，该模块中的所有代码现在将不区分大小写地比较文本。
CAT cat CaT cAt
猫猫猫猫猫
...would all match.
...都会匹配。
回答by Micah
Building on Siddharth's response, if you want to highlight all instances of duplicates other than the first (to make it easy to simply select all that show up and eliminate them), you could use this modification of his string:
基于 Siddharth 的回应，如果您想突出显示除第一个之外的所有重复实例（以便轻松选择所有显示并消除它们），您可以使用他的字符串的以下修改：
=IF(COUNTIF(A$1:A2,A2)>1,"D","S").
=IF(COUNTIF(A$1:A2,A2)>1,"D","S").
For conditional formatting, it would be like
对于条件格式，就像
=COUNTIF(A$1:A2,A2)>1.
=COUNTIF(A$1:A2,A2)>1.
This checks only the rows above the current cell, so the first instance of a duplicate will not be highlighted (as it won't have any duplicates above it).
这仅检查当前单元格上方的行，因此不会突出显示重复项的第一个实例（因为它上面没有任何重复项）。
回答by Neil.Corbin
For a function returning a boolean try...
对于返回布尔值的函数，尝试...
Option Explicit
Public Function DUPLICATE_VALUE(rngMyRange As Range, rngMyCell As Range) As Boolean
If WorksheetFunction.CountIf(rngMyRange, rngMyCell.Value) > 1 Then
DUPLICATE_VALUE = True
Else:
DUPLICATE_VALUE = False
End If
End Function
