VBA Remove Duplicates

What is Remove Duplicates in VBA Excel?

The VBA Remove Duplicates method allows you to remove duplicate values from a range or a specific column within a worksheet. This method is advantageous when cleaning your data by eliminating redundant or repeated entries, leaving unique values behind. VBA Remove Duplicates offers flexibility in specifying the columns you want to consider when identifying duplicates and whether to keep the first or last occurrence of a duplicate value.

Let us look at an example. Here, we have an Excel worksheet with customer IDs in Column A and their corresponding sales values in Column B. We must remove duplicate customer IDs while retaining the first occurrence.

VBA Remove Duplicates - Definition Example - 1

By utilizing the VBA Remove Duplicates method, we can achieve this. Using “ws.Range(“A:B”),” you specify the range that encompasses both columns.

The VBA Remove Duplicates method is then applied to this range, with “Columns:=Array(1)” indicating that only the first column, representing Customer IDs, should be considered for duplicate removal.

VBA Remove Duplicates - Definition Example - 2

When executed, this VBA code effectively cleans the data by removing duplicate customer IDs, leaving the initial entries intact.

VBA Remove Duplicates - Definition Example - 3

What is Remove Duplicates in VBA Excel?

The VBA Remove Duplicates method helps eliminate duplicate values from a specified range or columns in Excel.
You can customize the code to remove duplicates based on one or more columns, and whether your data has headers.
Always back up your data before using VBA Remove Duplicates to avoid accidental data loss.
To execute VBA Remove Duplicates from Array, transfer the array to a worksheet, apply VBA Remove Duplicates, and then retrieve the unique values back into an array.

How to Remove Duplicate Values in VBA Coding?

Here are the steps to remove duplicate values in VBA coding:

Open Excel and access the VBA Editor by pressing ALT + F11.
In the VBA editor, insert a new module by clicking on “Insert” in the menu and selecting “Module.”
Within the newly created module, define a VBA subroutine.
It will be the container for your code that removes duplicates.
Identify the data range from which you want to remove duplicates within your Excel worksheet.
You’ll need to specify this range in your VBA code.
Determine the criteria for identifying duplicates.
Write the VBA code to remove duplicates based on your chosen criteria.
You will use the RemoveDuplicates method to accomplish this. Ensure that your code specifies the correct range and criteria.
Test your code by running the macro within the VBA editor.
You can do this by pressing ALT + F8 to open the “Macro” dialog box, selecting your macro, and clicking “Run.”
Review your worksheet after running the macro to confirm that duplicate values have been removed per your code’s criteria.
Save your Excel workbook to preserve the changes made by the VBA code.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Start Learning Now

Examples

Example #1

Suppose you have a list of alphabets in Column A, and you want to remove duplicate letters:

Step 1: Start by creating a new subroutine named RemoveNameDuplicates in the new module.

Step 2: Next, we declare a variable ws of type Worksheet. It’s used to reference the worksheet where the data is located.

Step 3: Here, we set the ws variable to refer to the currently active worksheet within the workbook where the VBA code is running (ThisWorkbook refers to the workbook containing the code).

Step 4: Specify the range where duplicates should be removed, which is Column A in the active worksheet (ws.Range(“A:A”)).

The VBA Remove Duplicates method is applied to this range:

Columns:=Array(1) indicates that only the first column should be considered when identifying duplicates (in this case, Column A).

Header:=xlNo specifies that there are no headers in the data. If there were headers, you would use Header:=xlYes.

Step 5: Save the macro and close the VBE. Then, go to the desired worksheet, press Alt + F8, choose the macro “RemoveNameDuplicates,” and click “Run.”

Step 6: Now, you’ll observe that the repeated alphabets in Column A have been removed.

VBA Remove Duplicates in Excel - Example 1 - Step 6

Here is the complete code:

Sub RemoveNameDuplicates()
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
ws.Range(“A:A”).RemoveDuplicates Columns:=Array(1), Header:=xlNo
End Sub

Example #2

Imagine you have a list of transactions with multiple columns, and you want to remove duplicates based on a combination of the “Date” (Column A) and “Amount” (Column B).

Step 1: In the new module, define a new subroutine named “RemoveTransactionDuplicates.”

Step 2: Next, declare the ws variable of type Worksheet to reference the active worksheet.

Step 3: Now, we set the ws variable to the active worksheet.

Step 4: Here, we will specify the range where duplicates should be removed, which is Columns A and B in the active worksheet (ws.Range(“A:B”)).

The VBA Remove Duplicates from column method is applied to this range.

Columns:=Array(1, 2) indicates that both the first and second columns (Date and Amount) should be considered when identifying duplicates.

Header:=xlYes specifies that there are headers in the data.

Step 5: Save the macro and go to the desired worksheet, press Alt + F8, choose the macro, and click “Run.”

Step 6: After running the macro, you’ll observe that Columns A and B duplicate values have been eliminated.

VBA Remove Duplicates in Excel - Example 2 - Step 6

Here is the full code:

Sub RemoveTransactionDuplicates()
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
ws.Range(“A:B”).RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

Example #3

Suppose you have data in Columns A, B, and C, and you want to remove duplicates based on all three columns. This can be achieved using the VBA remove duplicates from column feature.

Step 1: Start by creating a new subroutine named “RemoveMultiColumnDuplicates” in the new module.

Step 2: Next, we declare the ws variable of type Worksheet to reference the active worksheet.

Step 3: Set the ws variable to the active worksheet.

Step 4: Specify the range where duplicates should be removed, which is Columns A, B, and C in the active worksheet (ws.Range(“A:C”)).

The VBA Remove Duplicates from column method is applied to this range.

Columns:=Array(1, 2, 3) indicates that all three columns should be considered when identifying duplicates.
Header:=xlYes specifies that there are headers in the data.

Step 5: Now, save the macro and close the VBE. Then, go to the desired worksheet, press Alt + F8, choose the macro “RemoveMultiColumnDuplicates,” and execute the macro.

Step 6: After running the VBA macro, you’ll see that any duplicate entries in the car brands, models, and costs columns have been eliminated wherever all three columns have identical data.

Excel VBA Remove Duplicates - Example 3 - Step 6

Here is the full code:

Sub RemoveMultiColumnDuplicates()
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
ws.Range(“A:C”).RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End Sub

Important Things to Note

The Columns argument in the Remove Duplicates method allows you to specify which columns should be considered when identifying duplicates. You can pass an array of column indices to include multiple columns.
The Header argument specifies whether your data has headers (e.g., column names). Use xlYes if your data has headers and xlNo if it doesn’t.
The Remove Duplicates method removes duplicate rows entirely, so be cautious and ensure you have a data backup before running this code.
To remove duplicates from an array in VBA, you can first transfer the array to a worksheet, use Remove Duplicates, and then retrieve the unique values back into an array.
VBA Remove Duplicates from Table is similar to removing them from a column. However, you would first reference the table as a range and then apply the VBA Remove Duplicates method to that range.

Frequently Asked Questions (FAQs)

1. How to remove duplicates in VBA without deleting rows?

You can remove duplicates in VBA without deleting rows by using the Remove Duplicates method. This method identifies and eliminates duplicate values within the specified range or columns without deleting entire rows.

2. How to remove duplicates in VBA based on one column?

To remove duplicates based on one column in VBA, specify that column in the Columns argument of the Remove Duplicates method, like this: ws.Range(“A:A”).RemoveDuplicates Columns:=Array(1), Header:=xlNo. This will remove duplicates from Column A.

3. Why is VBA Remove Duplicates not working?

VBA Remove Duplicates may not work correctly if there are issues with the specified range or columns, or if there are inconsistencies in the data. Ensure that the range is correctly defined, and the data format matches the expectations set in the code. Additionally, check for any error messages in the VBA editor for more specific information.

Download Template

This article must be helpful to understand the VBA Remove Duplicates, with its formula and examples. You can download the template here to use it instantly.

VBA Remove Duplicates Download

VBA Remove Duplicates

What is Remove Duplicates in VBA Excel?

Table of contents

Key Takeaways

How to Remove Duplicate Values in VBA Coding?