Removing *all* duplicate rows in Excel

Factual Questions
1

I know how to use the remove duplicates feature in Excel 2010. That is not what I need to do. The remove duplicates feature removes the second and all subsequent duplicate entries. I want to remove *all *of the duplicate rows, including the first duplicated row, leaving *only *rows that did not have any duplicates at all.

Any ideas?

2

Let’s say you have your data in column A. In a new column (perhaps B), use the formula:



=IF(COUNTIF(A:A,A1) > 1,"",A1)

Fill down throughout column B. Then column B will only show unique entries.

3

(With the implication being you can then use the unique values to make further modifications, perhaps copying only those rows to a new spreadsheet minus the duplicates, etc.)

4

You can use the conditional formatting feature to highlight all duplicate rows, then filter by cell color from the right click menu, then delete rows from the right click menu:

  1. Select all items
  2. Conditional Formatting -> Highlight cell rules -> Duplicate Values -> OK
  3. Right click in selection -> Filter -> Filter by selected cell’s color
  4. Right click in selection -> Delete Row -> OK
5

This works much better :slight_smile:

6

How can i do this in vba without manually deleting all duplicate rows. Every example i have found so far only deletes the duplicates not the original as well. Thanks

7

Simply have VBA do one of the two methods provided above

8

Easiest would be to turn on macro recording, then follow the steps in JWT Kottekoe’s post. Voila, you have VBA.