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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel vba remove duplicates

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.

Removing Duplicates from Fixed Cell Range Using VBA in Excel

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

Writing VBA Cdde to remove duplicates using VBA in Excel

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.

Running Macro to remove duplicates using VBA in Excel

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

Final output of method 1 to remove duplicates using VBA in Excel

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:

Sub Remove_Duplicates_fromRange()
Dim input_range As Range
Set input_range = Selection
input_range.RemoveDuplicates columns:=1, Header:=xlYes
End Sub
Deleting Duplicates from Selected Cell Range using VBA code in Excel

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.

Running Macro to remove duplicates using VBA in Excel

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

Final output of method 2 to remove duplicates using VBA in Excel

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


Similar Readings


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:

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
Removing Duplicates from Multiple Columns using VBA Code in Excel

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.

Running Macro to remove duplicates using VBA in Excel

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

Final output of method 3 to remove duplicates using VBA in Excel


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

Removing Duplicates from Table using VBA code in Excel

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.

Running Macro to remove duplicates using VBA in Excel

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

Final output of method 4 to remove duplicates using VBA in Excel


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:

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
  • Removing Duplicates from Rows of Data using VBA code in ExcelNow, 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.

Running Macro to remove duplicates using VBA in Excel

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

Final output of method 5 to remove duplicates using VBA in Excel


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:

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

Removing Duplicates from Array using VBA code in Excel

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.

Final output of method 6 Deleting Duplicates from Array


Practice Section

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

practice section Deleting Duplicates from Array

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

 

 

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo