删除具有重复数据的行 VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23820173/
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 Rows With Duplicate Data VBA
提问by mks212
I am struggling with something that should be fairly straightforward, however, I have read at least 15 methods of doing this and cannot seem to get it to work.
我正在努力解决一些应该相当简单的事情，但是，我已经阅读了至少 15 种这样做的方法，但似乎无法让它发挥作用。
Here is a sample dataset:
这是一个示例数据集：
9:30:01 584.7
9:30:01 590
9:30:01 595
9:30:02 584.51
9:30:03 584.62
9:30:04 584.44
9:30:05 584.05
I only want one row per second, so of the first 3 rows, only one needs to stay. I don't care if it is the first or the last, but the code I have been using keeps the last, 595 in this case.
我每秒只想要一行，所以前 3 行中，只有一个需要留下来。我不在乎它是第一个还是最后一个，但是我一直使用的代码保留了最后一个，在这种情况下为 595。
The way I am doing it is with a for loop that clears the contents of the row that has the same time as the row below it. I then sort the entire range.
我这样做的方式是使用 for 循环清除与其下方行具有相同时间的行的内容。然后我对整个范围进行排序。
I imagine there is a simpler way to simply delete the extra row from the get go. However, when I use delete on the range, instead of clear, it won't remove all of the duplicate rows.
我想有一种更简单的方法可以从一开始就简单地删除额外的行。但是，当我在范围上使用 delete 时，它不会删除所有重复的行，而不是清除。
Here is what I want the data to look like:
这是我希望数据的样子：
9:30:01 595
9:30:02 584.51
9:30:03 584.62
9:30:04 584.44
9:30:05 584.05
I need this to happen for the entire sheet. The time is Column B and the values are column C.
我需要整个工作表都发生这种情况。时间是 B 列，值是 C 列。
Here is the code I am using,
这是我正在使用的代码，
LastRow = ActiveSheet.UsedRange.row - 1 + _
ActiveSheet.UsedRange.Rows.Count
For RowNum = 2 To LastRow
If (Range("B" & RowNum) = Range("B" & RowNum + 1)) Then
Range("B" & RowNum).EntireRow.Clear
End If
Next RowNum
Range("A2:C" & LastRow).Sort key1:=Range("B2:B" & LastRow), _
order1:=xlAscending, Header:=xlNo
回答by teylyn
Don't loop. Use RemoveDuplicates. Way faster than any loop. One line of code.
不要循环。使用 RemoveDuplicates。比任何循环都快。一行代码。
Sub test()
ActiveSheet.Range("B:C").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub
Edit: screenshots
编辑：截图
BEFORE
前
AFTER
后
Edit: Does not work in Excel 2011 for Mac (go figure).
编辑：在 Excel 2011 for Mac 中不起作用（去图）。
回答by Jzz
This should do the trick:
这应该可以解决问题：
Sub jzz()
Dim i As Long
For i = 1 To Cells.SpecialCells(xlLastCell).Row 'loop from row 1 to last row
If Cells(i, 1) <> vbNullString Then 'check if something is in the cell
If Cells(i, 1) = Cells(i + 1, 1) Then 'check if cell is the same as next cell
Cells(i + 1, 1).EntireRow.Delete 'if so; delete
i = i - 1 'go back one row
End If
End If
Next i
End Sub
Another option is to go from the bottom up, like so:
另一种选择是自下而上，如下所示：
Sub jzz()
Dim i As Long
For i = Cells.SpecialCells(xlLastCell).Row to 1 step -1'loop from last row to row 1
If Cells(i, 1) <> vbNullString Then 'check if something is in the cell
If Cells(i, 1) = Cells(i + 1, 1) Then 'check if cell is the same as next cell
Cells(i + 1, 1).EntireRow.Delete 'if so; delete
End If
End If
Next i
End Sub
what you prefer to use if personal. Please consider the answer of teylyn as well, in this situation, it is very usable.
如果是个人的，您更喜欢使用什么。也请考虑 teylyn 的回答，在这种情况下，它非常有用。
