JIYIK CN >

We will explain how to remove duplicates in VBA by example.

Remove duplicates in VBA

When working with an Excel worksheet that contains a lot of data, it is likely that this data contains some duplicates. Any duplicates must be removed to avoid any miscalculations. To do this, we first have to clean the 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 where the data is located and then we will use the function on that range RemoveDuplicates. We also need to define how many columns we have to check for duplicates. If we have headers in the range, we also have to mark the headers as yes. Let's go through an example and use this function. First, we will create the demo data as shown below.

Let's use RemoveDuplicatesthe function to remove duplicates.

Sample code:

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

Output:

RemoveDuplicatesMethod is used for ranges. If duplicates are found, we can remove any row but keep all the values ​​of the original row.

RemoveDuplicatesMethods apply only to columns, not rows.

Deleting duplicate columns in VBA

Taking data from only one column to remove duplicates can be dangerous when removing duplicates. Data that is repeated in one column may have different content in the next column, making it unique rather than a duplicate.

We can easily compare RemoveDuplicatesmultiple columns in the compare method by passing them in an array as shown below.

Sample code:

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

Output:

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