Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

How to remove duplicates in excel

In this lesson I will explain you how to remove duplicates in excel. We often deal with excel or csv files which contain duplicate values. Sometimes duplicate values add meaning to the data. But sometimes these duplicates are troublesome. Specially when we analyze data. Now I will explain step by step, how to remove duplicates from your excel or csv files. Let’s consider this sample excel file. As you can see, I have highlighted the duplicates in this excel sheet.


Sample data with duplicate values

To remove duplicates, first click on one cell inside the data range. Then go to the “Data” tab and click on “Remove Duplicates”

Go to Data tab

“Remove Duplicates” dialog box will pop like this.

Remove duplicates dialog box

Select all the checkboxes and click OK. You will get result like this.

result data

Also we can do that using a vba macro as well. Below is the code to do that.

Sub RemoveDuplicates()

ActiveSheet.Range("$A$1:$C$20").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

End Sub

In above example, values will be removed only if columns A,B and C all have same data. Next let’s learn how to remove rows if a particular column has same data. Let’s consider this sample data set.

sample data

In this example sheet, I have highlighted the duplicate values we can find in column A. As you can see if we consider both column A and B then data are not same. In this example we are going to remove duplicates only based on column A. To do that first click on one cell within the data set. And then go to “Data” tab like in previous example and click on “Remove Duplicates”. Then remove duplicates dialog box will appear. In this dialog box only put tick to the “First Name” checkbox.

Remove duplicates dialog box

Then click OK and you will get this result.

Result data

Also you can use below macro to do it automatically as well.

Sub RemoveDuplicatesFromColA()

ActiveSheet.Range("$A$1:$B$17").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

Contact Form

Name

Email *

Message *