vba excel：检查基于 3 列的重复行并保留一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21660648/
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
excel: check for duplicate rows based on 3 columns and keep one row
提问by BlueSun3k1
This might be a bit much to ask but I'm wondering if the following is possible. this is all based on my initial question on Copy Range and Paste Values in another Sheet's specific Range
这可能有点多，但我想知道以下是否可行。这完全基于我关于复制范围和粘贴值在另一个工作表的特定范围内的初始问题
I have a sheet that contains approximately 12 columns where the same type of data is copied to which means that two or more rows could contains the exact same data, however if I could check for duplicates it would be based on 3 specific columns.
我有一个包含大约 12 列的工作表，其中复制了相同类型的数据，这意味着两行或更多行可能包含完全相同的数据，但是如果我可以检查重复项，它将基于 3 个特定列。
I don't want to use AutoFilter or Conditional Formatting for this task because the sheet can contain hundreds to thousands of rows and I want to be able to locate duplicates, delete them and keep just one original. I have also read other posts on here about Removing Duplicates based on column conditions but so far none has worked for me.
我不想为此任务使用自动筛选或条件格式，因为工作表可能包含数百到数千行，我希望能够找到重复项、删除它们并只保留一个原始行。我还在这里阅读了其他关于基于列条件删除重复项的帖子，但到目前为止没有任何帖子对我有用。
The Range of data is from A thru P and down and the 3 columns that would identify a duplicate are A (which is numeric, an ID), B (which is a date), and P (which is a Text string). Where this data comes from, it will always have the same format and will always be placed in the same columns which is why if A,B,P are the same, then it is considered a duplicate.
数据范围是从 A 到 P 和向下，并且可以识别重复项的 3 列是 A（这是数字，一个 ID）、B（这是一个日期）和 P（这是一个文本字符串）。这些数据来自哪里，它总是具有相同的格式，并且总是放置在相同的列中，这就是为什么如果 A、B、P 相同，那么它被认为是重复的。
Thanks for the help in advance.
我在这里先向您的帮助表示感谢。
回答by Dmitry Pavliv
This code works for me (lastrowbased on this asnwerby @SiddharthRout):
这段代码对我有用（lastrow基于@SiddharthRout 的这个答案）：
Sub test()
Dim lastrow As Long
With ThisWorkbook.Worksheets("Sheet1")
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
lastrow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
lastrow = 1
End If
'Array(1, 2, 16) means 1 - for A, 2 for B and 16 for P columns
.Range("A1:P" & lastrow).RemoveDuplicates Columns:=Array(1, 2, 16), _
Header:=xlYes
End With
End Sub
