删除所有重复行 Excel vba
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16988816/
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
Delete all duplicate rows Excel vba
提问by fbonetti
I have a worksheet with two columns: Date and Name. I want to delete all rows that are exact duplicates, leaving only unique values.
我有一个包含两列的工作表：日期和名称。我想删除所有完全重复的行，只留下唯一值。
Here is my code (which doesn't work):
这是我的代码（不起作用）：
Sub DeleteRows()
Dim rng As Range
Dim counter As Long, numRows As Long
With ActiveSheet
Set rng = ActiveSheet.Range("A1:B" & LastRowB)
End With
numRows = rng.Rows.Count
For counter = numRows To 1 Step -1
If rng.Cells(counter) Like rng.Cells(counter) - 1 Then
rng.Cells(counter).EntireRow.Delete
End If
Next
End Sub
It's "Like rng.Cells(counter)-1" that seems to be the cause- I get "Type Mismatch".
似乎是“像 rng.Cells(counter)-1”这样的原因——我得到“类型不匹配”。
回答by fbonetti
There's a RemoveDuplicatesmethod that you could use:
有一种RemoveDuplicates方法可以使用：
Sub DeleteRows()
With ActiveSheet
Set Rng = Range("A1", Range("B1").End(xlDown))
Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End With
End Sub
回答by kadrleyn
The duplicate values in any column can be deleted with a simple for loop.
可以使用简单的 for 循环删除任何列中的重复值。
Sub remove()
Dim a As Long
For a = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If WorksheetFunction.CountIf(Range("A1:A" & a), Cells(a, 1)) > 1 Then Rows(a).Delete
Next
End Sub
