How to Remove Duplicates in VBA
We will introduce how to remove duplicates in the VBA with examples.
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.
Let’s use the RemoveDuplicates function to remove the duplicates.
Example Code:
# VBA
Sub duplicateRemover()
Range("A1:B*").RemoveDuplicates Columns:=1
End Sub
Output:
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.
Example Code:
# Vba
Sub duplicateRemover()
ActiveSheet.UsedRange.RemoveDuplicates Columns:=Array(1, 2)
End Sub
Output:
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.