Rifat Hassan
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.
- 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
Code Breakdown
- Firstly, we initiated a sub procedure named Remove_Duplicates_from_fixedRange.
- After that, we used the RemoveDuplicates Method to specify the range and remove the duplicates from the specified range.
- Finally, we ended the sub procedure.
- 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
Code Breakdown
- Firstly, we initiated a sub procedure named Remove_Duplicates_from_fixedRange.
- After that, we introduced a variable input_range as Range.
- Following that, we used the Set statement to assign the selected range in the input_range variable.
- After that, we used the RemoveDuplicates Method to remove the duplicates from the selected range.
- Finally, we ended the sub procedure.
- 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
Code Breakdown
- Firstly, we initiated a sub-procedure named Remove_Dups_from_MultipleCoulmns.
- After that, we introduced a variable input_rng as Range.
- Following that, we used the Set statement to assign the selected range in the input_rng variable.
- After that, we used the RemoveDuplicates Method to remove the duplicates from the selected range.
- Finally, we ended the sub-procedure.
- 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
Code Breakdown
- Firstly, we initiated a sub procedure named removing_dupllicate_fromTable.
- After that, we used the RemoveDuplicates Method to remove the duplicates from the selected Table.
- Finally, we ended the sub procedure.
- 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.
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
Code Breakdown
- Firstly, we introduced a function named delete_duplicates.
- Then, we declared the argument of the function along with its data type.
- After that, we declared a variable named my_array as Variant.
- Now, we used the ReDim statement to resize the my_array variable.
- Then, we used the For Next loop to assign the values of the input_range into the my_array variable.
- Following that, we set the value of the Count as 0.
- After that, we use two nested For Next loops and an IF statement to replace any value with a blank if it is a duplicate value.
- Then, we used the Redim Preserve statement to edit the array size of the my_array variable.
- Now, we used the WorksheetFunction.Transpose Method to transpose the values of the my_array variable.
- Finally, we terminated the 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.
=delete_duplicates(B5:B15)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]
