VBA Remove Duplicates

Updated June 1, 2023

VBA Remove Duplicates

Excel has a feature for removing duplicate values from the selected cells, rows, or tables. What if this process we automate in VBA? Yes, the process of removing the duplicate can be automated in VBA in the form of Macro. In the process of removing the duplicate, once it is completed, the unique values remain in the list or table. This can be in with the help of Remove Duplicates function in VBA.

How to Use Excel VBA Remove Duplicates?

We will learn how to use a VBA Remove Duplicates with a few examples in Excel.

Example #1 – VBA Remove Duplicates

We only have a list of numbers from 1 to 5 until row 20 in column A. As we can see in the screenshot below, all the numbers are repeated multiple times.

Now our job is to remove the duplicate from the list by VBA. For this, go to the VBA window by pressing the F11 key.

In this example, we will see how VBA Remove Duplicates can work for numbers. For this, we need a Module.

Step 1: Open a new Module from the Insert menu in the Insert menu tab.

Step 2: Once it is open, write the subcategory of VBA Remove Duplicate as shown below.

Code:

Sub VBARemoveDuplicate1()

End Sub

Step 3: In the process of removing the duplicate, first, we need to select the data. For this, in VBA, we will use the Selection function till it goes down to select the complete data list, as shown below.

Code:

Sub VBARemoveDuplicate1()

Selection.End(xlDown).Select

End Sub

Step 4: Now, we will select the Range of selected cells or columns A. It will go down till we have the data in a particular column. Not only till row 20.

Code:

Sub VBARemoveDuplicate1()

Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select

End Sub

Step 5: Now select the range of the cells in a currently opened sheet, as shown below. This will activate the complete column. We have selected column A till the end.

Code:

Sub VBARemoveDuplicate1()

Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Range("A:A").

End Sub

Step 6: Now, use the RemoveDuplicate function here. This will activate the command to remove the duplicate values from the sequence of column 1. If there are more columns, then the number will be added and separated by commas in the brackets as (1, 2, 3,…).

Code:

Sub VBARemoveDuplicate1()

Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1,

End Sub

Step 7: Now, we will use the Header command, which will move the cursor to the top cell of the sheet, which is mainly in the header of any table.

Code:

Sub VBARemoveDuplicate1()

Selection.End(xlDown).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

Step 8: Compile the code steps by pressing the F8 Key. Once done, click the Play button to run to code as shown below.

As we can see, the duplicate number is deleted from column A, and only a unique count is left.

Example #2 – VBA Remove Duplicates

In this example, we will see how to remove duplicate values from more than one column. For this, we will consider the same duplicate list used in example-1. But in a new way, we have added 2 more columns of the same values as shown below.

This is another method with a little different type of code structure.

Step 1: Open a new module in VBA and write the subcategory in the VBA Remove Duplicate. If possible, give it a sequence number so it will be better to choose the right code to run.

Code:

Sub VBARemoveDuplicate2()

End Sub

Step 2: Select the complete sheet in VBA as shown below.

Code:

Sub VBARemoveDuplicate2()

Cells.Select

End Sub

Step 3: Select the currently opened sheet with the ActiveSheet command and select columns A to C, as shown below.

Code:

Sub VBARemoveDuplicate2()

Cells.Select
ActiveSheet.Range("A:C").

End Sub

Step 4: Now select the RemoveDuplicates command, and after that, select Column array from 1 to 3, as shown below.

Code:

Sub VBARemoveDuplicate2()

    Cells.Select
    ActiveSheet.Range("A:C").RemoveDuplicates Columns:=Array(1, 2, 3),

End Sub

Step 5: At last use, the Header command will be included in removing duplicates as xlYes, as shown below.

Code:

Sub VBARemoveDuplicate2()

    Cells.Select
    ActiveSheet.Range("A:C").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes

End Sub

Step 6: Now compile the complete code and run. As we can see below, the complete sheet is selected, but the duplicate values are removed from columns A, B, and C, keeping a unique count.

Example #3 – VBA Remove Duplicates

This is another method of removing duplicates which is the simplest way to remove duplicates in VBA. For this, we will use the data we saw in example-1 and below.

Step 1: Now go to VBA and write a subcategory of VBA Remove Duplicates again. We have given the sequence to each code we showed to have a proper track.

Code:

Sub VBARemoveDuplicate3()

End Sub

Step 2: This is quite a similar pattern to what we have seen in example-2 but a shortcut way to write code for removing duplicates. For this, first, directly start selecting the range of columns below. We have kept the limit till the 100^th cell of column A starting from 1 followed by a dot(.)

Code:

Sub VBARemoveDuplicate3()

Range("A1:A100").

End Sub

Step 3: Now select the RemoveDuplicates command, as shown below.

Code:

Sub VBARemoveDuplicate3()

Range("A1:A100").RemoveDuplicates

End Sub

Step 4: Now select columns A with the command Columns with the sequence of 1. And then include the header of the columns chosen, as shown below.

Code:

Sub VBARemoveDuplicate3()

Range("A1:A100").RemoveDuplicates Columns:=1, Header:=xlYes

End Sub

Step 5: Now, compile it by pressing the F8 key and running. We will see our code has removed the duplicate numbers from column A, and unique values pertain.

Pros of VBA Remove Duplicates

• It is helpful in quickly removing duplicates in any range of cells.
• It is easy to implement.
• When working on a huge data set, where removing the duplicate becomes difficult manually and hangs the files, VBA Remove Duplicates works in a second to give us unique values.

Cons of VBA Remove Duplicates

• It is not beneficial to use VBA Remove Duplicates for very small data, as the Remove Duplicate function could be quickly done in the Data menu bar.

Things to Remember

• The range can be selected in two ways. Once it is selected, the limit of cells, as shown in example-1, and the other is selecting the complete column till the end, as shown in example-1.
• Please ensure the file is saved in Macro-Enabled Excel, allowing us to use the written code multiple times without losing it.
• You can keep the value of the function Header as Yes, as it will also count the header while removing the duplicate values. If there is no duplicate value with the name of the Header’s name, then keeping it as No will harm nothing.

Recommended Articles

This has been a guide to VBA Remove Duplicates. Here we have discussed how to use Excel VBA Remove Duplicates, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

1. VBA Active Cell
2. VBA XML
3. Excel Wrap Text
4. VBA Month