Excel & VBA

23 Feb 2018

Highlight Duplicates in Column

Posted By

The Result – Highlight Duplicates

Quickly highlight which entries in your data set are duplicates. If you suffer from deletion anxiety, this is the far better option!

Highlight Duplicates

Figure 1: Highlight Duplicates

The Problem

So when working with large data sets it’s a common occurrence to have duplicate entries. Often we would like to weed these out! Our first port of call would be to use the in-built Excel function ‘Remove Duplicates’ (Click Here For More Info), however, what if we first want to VISUALLY see which entries are duplicates instead of just having excel delete them. We can use some VBA and some clever error handling to ‘Find’ duplicates and color them RED.

The Code

The code above currently looks at the data from cell A2 downwards. If you wish to search through a different column make sure you change the column reference on Line 9. So now that you can see the offending entries, what if you also want to quickly delete them?

Goodbye Duplicates

Figure 2: Goodbye Duplicates

If you want the option to also delete those pesky duplicate rows, we can introduce a small code edit as shown below.

Now sit back and enjoy your clean duplicate free workbook. Maybe hold a small ceremony in the office to mark the occasion!


It takes many years of hard work... to become and 'overnight success' - Gary Vaynerchuk

Leave a Reply