Remove Duplicates in VBA

  1. Remove Duplicates in VBA
  2. Remove Duplicate Multiple Columns in VBA

We will introduce how to remove duplicates in the VBA with examples.

How to Clear Multiple Textbox in VB...
How to Clear Multiple Textbox in VB.Net using Visual Studio 2015 | 2020 Best Practices

Remove Duplicates in VBA

When working with excel sheets with data in bulk, there are chances that these data contain some duplicates. It is essential to remove any duplicates to avoid any wrong calculations. For this purpose, we first have to clean our data and remove the duplicates. VBA provides a simple function, RemoveDuplicates, that can be used for this purpose.

We need to first select the range in which our data lies, and then we will use the RemoveDuplicates function on that range. We also need to define how many columns must be checked for duplicates. If we have headers in range, we must also mark the header as a yes. Let’s go through an example and use this function. First of all, we will create demo data as shown below.

demo data for removing duplicates in VBA

Let’s use the RemoveDuplicates function to remove the duplicates.

Example Code:

# VBA
Sub duplicateRemover()
Range("A1:B*").RemoveDuplicates Columns:=1
End Sub

Output:

demo data after removing duplicates in VBA

The RemoveDuplicates method is used on a range. If duplicates are found, we can remove any row but keep the original row with all values.

The RemoveDuplicates method only works on columns, not on rows.

Remove Duplicate Multiple Columns in VBA

While removing duplicates, it can be dangerous to remove duplicates by only taking in data from one column. The data duplicated in one column can have something different in the next, making it a unique item instead of a duplicate.

We can easily compare multiple columns in the RemoveDuplicates method by passing multiple columns through an array, as shown below.

demo data for comparing multiple columns in RemoveDuplcate method

Example Code:

# Vba
Sub duplicateRemover()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2)
End Sub

Output:

demo data after comparing multiple columns in RemoveDuplcate method

When we compared both columns, it only removed the columns that had duplicates based on both columns, not just one. There is no need to always provide columns in order, we can send columns that we want to compare in any order, and we can even use columns 1 and 5.