vba 检查所有单元格中的重复项
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/40717996/
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
Checking for duplicates across all cells
提问by JcDiago
How do I code this in the most simplest way?
我如何以最简单的方式对此进行编码？
If let's say Range("A1").value = "Thursday"
如果让我们说 Range("A1").value = "Thursday"
Check for duplicates on all the cells which has value in them (B1, C1, D1,...)
检查所有具有值的单元格上的重复项（B1、C1、D1、...）
If a duplicate is found, select 3 cells below each of those cells Range("B2:B4")and so on...
如果找到重复项，请选择每个单元格下方的 3 个单元格Range("B2:B4")，依此类推...
回答by JcDiago
The simplest way is to check for duplicates:
最简单的方法是检查重复项：
Using a WorksheetFunction
使用工作表函数
=COUNTIF(A:A,A1)>1
=COUNTIF(A:A,A1)>1
Using the VBA
使用 VBA
Dim Target As Range
Dim r As Range
Set Target = Range("A1", Range("A" & Rows.Count).End(xlUp))
For Each r In Target
r.Offset(0, 1) = WorksheetFunction.CountIf(Target, r.Value) > 1
Next
If you want to remove duplicates in the first column of the range
如果要删除范围第一列中的重复项
Target.RemoveDuplicates Columns:=1, Header:=xlNo
Target.RemoveDuplicates 列：=1，标题：=xlNo
If you wanted to expand your range to include Columns B and C
如果您想扩大范围以包括 B 列和 C 列
Set Target = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 3)
Set Target = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 3)
Remove duplicates in the first and third columns
删除第一列和第三列中的重复项
Target.RemoveDuplicates Columns:=Array(1, 3), Header:=xlNo
Target.RemoveDuplicates Columns:=Array(1, 3), Header:=xlNo
Remove rows that contain the duplicates
删除包含重复项的行
Target.EntireRow.RemoveDuplicates Columns:=Array(1, 3), Header:=xlNo
Target.EntireRow.RemoveDuplicates Columns:=Array(1, 3), Header:=xlNo
回答by Ravi Shankar Kota
Below code identifies duplicate value in a column and highlight with red. Hope this might be of some help.
下面的代码标识列中的重复值并用红色突出显示。希望这可能会有所帮助。
iLastRow = Cells(chosenExcelSheet.Rows.Count, 1).End(xlUp).Row 'Determine the last row to look at
Set rangeLocation = Range("A1:A" & iLastRow) 'Range can be updated as per your need
'Checking if duplicate values exists in same column
For Each myCell In rangeLocation
If WorksheetFunction.CountIf(rangeLocation, myCell.Value) > 1 Then
myCell.Interior.ColorIndex = 3'Highlight with red Color
Else
myCell.Interior.ColorIndex = 2'Retain white Color
End If
Next
