Delete Rows With Duplicate Data VBA

Question

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. 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. 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. 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 ``` I need this to happen for the entire sheet. The time is Colu…

Accepted Answer

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
```

Edit: screenshots

BEFORE

AFTER

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

Delete Rows With Duplicate Data VBA

Solution

Problem