How to Remove Duplicates Using VBA in Excel (6 Easy Ways)

One of the most important activities in Excel is to remove duplicates from a given data set. Today we will see how we can remove duplicates in Excel using VBA only. In this article, we will discuss 6 simple yet efficient methods to remove duplicates using VBA in Excel. So, let’s start this article and explore these methods.

Download Practice Workbook

6 Effective Methods to Remove Duplicates Using VBA in Excel

In this section of the article, we will learn 6 effective methods to remove duplicates using VBA in Excel. For instance, let’s say we have the Examination Record of Sunflower Kindergarten as our dataset. But there are some duplicate entries. Our goal is to remove these duplicates using VBA in Excel.

Not to mention, we used the Microsoft Excel 365 version for this article; however, you can use any version according to your preference.

1. Removing Duplicates from Fixed Cell Range

First of all, we will remove duplicates from a fixed and specified cell range. Here, our dataset is in the range B4:E15. We will use this fixed cell range in the VBA code. Now, let’s follow the steps mentioned below.

Step 01: Create a New Module

• Firstly, go to the Developer tab from Ribbon.
• Following that, click on the Visual Basic option from the Code group.

As a result, the Microsoft Visual Basic for Applications window will appear on your worksheet as shown in the following image.

• Now, in the Microsoft Visual Basic for Applications window, go to the Insert tab.
• Then, choose the Module option from the drop-down.

Step 02: Write and Save VBA Code in Module 

• Firstly, write the following code in the newly created Module.

Sub Remove_Duplicates_from_fixedRange()
Range("B5:E15").RemoveDuplicates columns:=1, Header:=xlYes
End Sub

• After writing the code, click on the Save option.

Step 03: Run the Macro

• Now, use the keyboard shortcut ALT + F11 to go to the worksheet from the Microsoft Visual Basic for Applications window.
• Afterward, press the keyboard shortcut ALT + F8 to open the Macro dialogue box as shown in the following picture.

• Now, in the Macro dialogue box, choose the Remove_Duplicates_from_fixedRange option.
• Finally, click on Run.

Consequently, the duplicate values from the range B4:E15 will be removed as demonstrated in the image below.

Read More: How to Delete Duplicates in Excel but Keep One (7 Methods)

2. Deleting Duplicates from Selected Cell Range

Now, we will remove the duplicate values from a specific range that we will select. So, before running the Macro, we need to specify the selection range. So, let’s use the instructions outlined in the following section to do this.

Steps:

• Firstly, use the instructions mentioned in Step 01 of the first method to create a new Module.
• After you have created a new Module, write the following code in the blank Module.

Sub Remove_Duplicates_fromRange()
Dim input_range As Range
Set input_range = Selection
input_range.RemoveDuplicates columns:=1, Header:=xlYes
End Sub

• After writing the code, click on the Save option.

• After that, press the keyboard shortcut ALT + F11 to go to the worksheet from the Microsoft Visual Basic for Applications window.
• Now, select the range you want to remove the duplicates. In this case, we have selected the entire dataset.
• Subsequently, use the shortcut ALT + F8 to open the Macro dialogue box as shown in the following image.

• Now, in the Macro dialogue box, choose the Remove_Duplicates_fromRange option.
• Lastly, click on Run.

As a result, the duplicate values will be removed from your selected range as shown in the following image.

Read More: How to Remove Duplicate Rows in Excel (3 Ways)

Similar Readings

• How to Remove Duplicates from Column in Excel (3 Methods)
• Excel VBA: Remove Duplicates from an Array (2 Examples)
• How to Remove Duplicates and Keep the First Value in Excel (5 Methods)
• Remove Duplicate Rows Except for 1st Occurrence in Excel (7 Ways)
• How to Remove Both Duplicates in Excel (5 Easy Ways)

3. Eradicating Duplicates from Several Columns

In this example, we will eradicate duplicates from various columns simultaneously by using a VBA code in Excel. So, let’s use the procedure discussed below to do this.

Steps:

• Firstly, use the steps described in Step 01 of the first method to create a new Module.
• After creating a new Module, write the code below in the blank Module.

Sub Remove_Dups_from_MultipleCoulmns()
Dim input_rng As Range
Set input_rng = Selection
input_rng.RemoveDuplicates columns:=Array(1, 2), Header:=xlYes
End Sub

• After that, click on the Save option.

• Following that, use the shortcut ALT + F11 to go to the worksheet from the Microsoft Visual Basic for Applications window.
• Now, choose the range you want to remove the duplicates. In this case, we have chosen the whole dataset.
• Then, use the keyboard shortcut ALT + F8 to open the Macro dialogue box as shown in the following image.

• Now, in the Macro dialogue box, choose the Remove_Dups_from_MultipleCoulmns option.
• Finally, click on Run.

Consequently, the duplicate values will be removed from numerous columns as shown in the following picture.

4. Eliminating Duplicates from Table

In Excel, we often use Tables to represent our data. In this section of the article, we will learn to eliminate duplicates from a Table.

Steps:

• Firstly, use the instructions discussed in Step 01 of the first method to create a new Module.
• After you have created a new Module, write the following code in the blank Module.

Sub removing_dupllicate_fromTable()
ActiveSheet.ListObjects("Table1").DataBodyRange.RemoveDuplicates columns:=Array(1, 2), Header:=xlYes
End Sub

• After writing the code, click on the Save option.

• Following that, press the keyboard shortcut ALT + F11 to go to the worksheet from the Microsoft Visual Basic for Applications window.
• Then, select the Table from which you want to remove the duplicates.
• Afterward, use the shortcut ALT + F8 to open the Macro dialogue box as shown in the following image.

• Now, in the Macro dialogue box, choose the removing_dupllicate_fromTable option.
• Finally, click on Run.

Consequently, the duplicate values will be removed from the selected table as demonstrated in the image below.

5. Wiping out Duplicates from Rows of Data

Now, we will delete the duplicate rows using VBA. Here, we will consider a row as a duplicate row, if all the cells of a row are identical to another row. So, let’s follow the steps outlined below to do this.

Steps:

• Firstly, apply the procedure outlined in Step 01 of the first method to create a new Module.
• Following that, write the following code in the blank Module.

Sub delete_dups_from_RowsofData()
Dim my_range As Range
Dim columns As Variant
Set my_range = Range("B4:E15")
AllColumns = my_range.columns.Count
ReDim columns(0 To AllColumns - 1)
For i = 0 To AllColumns - 1
columns(i) = i + 1
Next i
my_range.RemoveDuplicates columns:=(columns), Header:=xlYes
End Sub

• Now, click on the Save option.

• Then, press the keyboard shortcut ALT + F11 to go to the worksheet from the Microsoft Visual Basic for Applications window.
• Now, use the shortcut ALT + F8 to open the Macro dialogue box as shown in the following image.

• After that, in the Macro dialogue box, choose the delete_dups_from_RowsofData option.
• Finally, click on Run.

Consequently, the duplicate rows will be removed from the dataset as shown in the image below.

6. Deleting Duplicates from Array

In this section of the article, we learn to delete duplicate values from a selected Array. To do this, we will create a user-defined function using VBA code. So, let’s use the instructions outlined in the following section.

Steps:

• Firstly, use the steps mentioned in Step 01 of the first method to create a new Module.
• After you have created a new Module, write the following code in the blank Module.

Function delete_duplicates(input_range As Range)

Dim my_array() As Variant
ReDim my_array(input_range.Rows.Count - 1)
For i = LBound(my_array) To UBound(my_array)
my_array(i) = input_range.Cells(i + 1, 1)
Next i

Count = 0
For i = LBound(my_array) To UBound(my_array) - Count
For j = LBound(my_array) To UBound(my_array) - Count2
If i <> j And my_array(i) = my_array(j) And my_array(i) <> "" Then
my_array(j) = ""
Count = Count + 1
End If
Next j
Next i

For i = LBound(my_array) To UBound(my_array)
If my_array(i) = "" Then
For j = i To UBound(my_array) - 1
my_array(j) = my_array(j + 1)
Next j
If i < UBound(my_array) - Count + 1 Then
i = i - 1
End If
End If
Next i
ReDim Preserve my_array(UBound(my_array) - Count)

delete_duplicates = Application.WorksheetFunction.Transpose(my_array)

End Function

• After writing the code, click on the Save option.

• Following that, press the keyboard shortcut ALT + F11 to go to the worksheet from the Microsoft Visual Basic for Applications window.
• Now, use the following formula in cell G5.

Here, the range of cells B5:B15 represents the selected Array.

• Afterward, press ENTER.

Consequently, the duplicate values from the selected range will be removed and you will get the following output on your worksheet.

Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it yourself.

Read More: Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)

Conclusion

So, these are the most common and effective methods you can use anytime while working with your Excel datasheet to remove duplicates using VBA  in Excel. If you have any questions, suggestions, or feedback related to this article, you can comment below. You can also have a look at our other useful articles on Excel functions and formulas on our website, ExcelDemy, a one-stop Excel solution provider.

Related Readings

• Fix: Excel Remove Duplicates Not Working (3 Solutions)
• How to Remove Duplicates Based on Criteria in Excel (4 Methods)
• Excel Formula to Automatically Remove Duplicates (3 Quick Methods)
• How to Remove Duplicates Using VLOOKUP in Excel (2 Methods)
• Remove duplicate rows based on two columns in Excel [4 ways]