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.

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

“Remove Duplicates” dialog box will pop like this.

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

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.

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.

Then click OK and you will get this result.

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

How to Print Automatically (Excel VBA)

In this post I will explain how we can take printouts automatically using vba. So let’s consider this sample sheet.

This is a employee ID of a company. We use this ID as a template to generate IDs for each and every employee of the company. Now let’s look at how we can print this ID. We can do it easily like this.

Sub PrintID() Dim WS As Worksheet Set WS = Worksheets("Sheet2") WS.Range("B2:J13").PrintOut End Sub

Here "Sheet2" is the name of the worksheet. And Range("B2:J13") is the area we need to print. So you should modify the sheet name and range according to your worksheet name and range, you want to print. If you run above code it will print the ID automatically.

So we learned how to take printout of a range we want. But what if we need several copies of that range. We can take several copies automatically using following code. This below code will print 2 copies of that same range.

Sub PrintID_2Copies() Dim WS As Worksheet Set WS = Worksheets("Sheet2") WS.Range("B2:J13").PrintOut Copies:=2 End Sub

Note that using this above code you can only print one ID. However if you need you can improve this macro to print IDs for all the employees in a database. Let’s assume you have a database with information of your employees. Then you can use For loop and cell addresses to create and print ID for each and every employee in that database.