Remove Duplicates with Excel VBA

Objective:

The goal is to write a VBA code that will remove duplicates from a selected range in an Excel worksheet. This can be useful when you have data in a list and want to clean it up by eliminating any repeated entries.

What does the VBA code do?

1. Identifies the range that contains data (either the selected range or an entire column).
2. Removes duplicates based on one or more columns.
3. Keeps the first occurrence and removes the rest of the duplicates in the specified range.
4. Provides feedback to the user about the number of duplicates removed.

Step-by-step Explanation:

1. Set the Range:
   • The code first identifies the range of cells where duplicates need to be removed. You can specify this range manually, or it can be the entire worksheet or a particular column.
2. Using RemoveDuplicates:
   • The RemoveDuplicates method is used in VBA to remove duplicate entries from the range. This method has the ability to specify which columns to check for duplicates.
3. Feedback for User:
   • After duplicates are removed, a message box will notify the user how many duplicates were removed, so the user can see the results of the operation.

Example Code:

Sub RemoveDuplicatesDetailed()
    ' Declare variables
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim result As Range
    Dim deletedCount As Integer
    ' Set the worksheet and range where you want to remove duplicates
    ' For example, using the active sheet and range from A1 to the last used row in column A.
    Set ws = ThisWorkbook.ActiveSheet
    Set dataRange = ws.Range("A1:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)  
    ' Confirming the range to be used
    MsgBox "Removing duplicates from: " & dataRange.Address
    ' Remove duplicates based on the first column (you can modify the column references here if needed)
    ' The RemoveDuplicates method requires an array of column indices for which to check duplicates.
    ' In this case, we are checking for duplicates in column 1 (Column A).   
    ' Removing duplicates from the range
    deletedCount = dataRange.RemoveDuplicates(Columns:=1, Header:=xlNo) ' xlNo indicates no header in range
    ' Provide feedback on how many duplicates were removed
    MsgBox deletedCount & " duplicates were removed from the range " & dataRange.Address, vbInformation, "Duplicates Removed"  
End Sub

Detailed Explanation of the Code:

1. Declare Variables:
   • ws: A variable that represents the worksheet where the operation is performed.
   • dataRange: A variable to hold the range of data that will have duplicates removed.
   • result: This could be used for capturing any result returned from the RemoveDuplicates function.
   • deletedCount: To store the number of deleted duplicates, this value can be returned by the RemoveDuplicates method.
2. Setting the Range:
   • Set ws = ThisWorkbook.ActiveSheet: This sets the worksheet that the operation will be applied to. In this case, the currently active worksheet is selected.
   • Set dataRange = ws.Range(« A1:A » & ws.Cells(ws.Rows.Count, « A »).End(xlUp).Row): This defines the range where duplicates will be removed. It starts from A1 and goes down to the last used row in column A. The .End(xlUp) method is used to find the last used row in the specified column.
3. Removing Duplicates:
   • deletedCount = dataRange.RemoveDuplicates(Columns:=1, Header:=xlNo): This is where duplicates are actually removed. The RemoveDuplicates method accepts two key arguments:
     • Columns: Specifies which columns to check for duplicates. In this case, it’s column 1 (which is column A).
     • Header: Tells Excel whether the first row contains headers (xlYes), or whether the range includes no headers (xlNo). In this example, we are assuming there are no headers, so we use xlNo.
4. Feedback:
   • After the duplicates are removed, a message box will appear with how many duplicates were removed (deletedCount) and the range where it happened (dataRange.Address).

Additional Modifications:

• Removing Duplicates Based on Multiple Columns:
  If you want to remove duplicates based on multiple columns (say, columns A and B), you can modify the Columns parameter as follows:
• deletedCount = dataRange.RemoveDuplicates(Columns:=Array(1, 2), Header:=xlNo)
• Handling Headers: If the data has headers in the first row, you can change the Header:=xlNo to Header:=xlYes in the RemoveDuplicates method to preserve the header row.

Conclusion:

This VBA code provides a simple way to remove duplicates from a selected range in Excel. You can further customize it to fit your needs, such as applying it to a specific sheet or removing duplicates across multiple columns. This approach is very efficient, especially when working with large datasets.