删除具有重复数据VBA的行 [英] Delete Rows With Duplicate Data VBA

问题描述

我正在努力做一些应该非常简单的事情，但是，我已经阅读了至少15种方法，而且似乎无法使其正常工作.

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.

这是一个示例数据集:

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

我每秒只需要一行，因此在前3行中，仅需保留一行.我不在乎它是第一个还是最后一个，但是我一直使用的代码会保留最后一个，在这种情况下为595.

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.

我的操作方式是使用for循环，该循环清除与下一行具有相同时间的行的内容.然后，我对整个范围进行排序.

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.

我想有一种更简单的方法，可以简单地从一开始就删除多余的行.但是，当我在范围上使用delete而不是clear时，它不会删除所有重复的行.

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.

这是我想要的数据外观:

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

我需要在整个工作表中进行此操作.时间是B列，值是C列.

I need this to happen for the entire sheet. The time is Column B and the values are column 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

推荐答案

不要循环.使用RemoveDuplicates.比任何循环都快.一行代码.

Don't loop. Use RemoveDuplicates. Way faster than any loop. One line of code.

Sub test()
    ActiveSheet.Range("B:C").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

屏幕截图

之前

之后

在Mac的Excel 2011中不起作用(如图).

Does not work in Excel 2011 for Mac (go figure).

这篇关于删除具有重复数据VBA的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！