How to remove duplicates using VBA in Microsoft Excel

In this tutorial we will learn how to remove duplicates in excel using VBA in Microsoft Excel.One common task in Excel is removing duplicates from a data set. While Excel provides a built-in feature for removing duplicates, it may not always be sufficient for complex data sets or specific needs. This is where VBA can be particularly useful in Excel. By using VBA to remove duplicates in Excel, you can customize the criteria for determining duplicates and automate the process to save time and reduce errors. 

Here we have a data set above having 3 duplicate rows i.e.1, 5, 7.We will remove these duplicate rows using VBA.

VBA (Visual Basic for Applications) is a powerful programming language that is integrated with Microsoft Office applications like Excel. It allows users to automate repetitive tasks, perform complex calculations, and create custom functions and applications that can greatly increase productivity.

Step 1 – Go to the Developer Tab

– Go to the Developer tab in the menu bar.

Step 2 – Click on the Visual Basic

– Click on the Visual Basic button on the right end of the ribbon.
– Visual Basic window will open.

Step 3 – Insert a New Module

– Right click on the sheet name in the Project-VBA Project menu at the left of the window.
– Click on Insert.
– Click on Module

Step 4 – Declare a Procedure

– Declare a procedure by entering “Sub Remove_Duplicates()”.

Step 5 – Enter the VBA Code

– Enter the VBA code: 
                         Range(“A1:F10”).RemoveDuplicates Columns:=2, Header:=xlYes

Where  Range(“A1:F10”) specifies the range having the duplicates to be removed.
Columns:=2 specify the column in the data from which you want to match the duplicates.
Header:=xlYes represents whether the data has headers or not.In case not you may enter Header:=xlNo.

Step 6 – Run the Code

– Run the code by clicking on the Run in the menu bar.
– Click on Run/Sub userForm option.
– The duplicates will be removed from the data and only the first instance will be left as shown above.