How To Remove Duplicates In Excel Vba

How to Remove Duplicates in Excel VBA

In Excel VBA, you can remove duplicates from a range of data using the RemoveDuplicates method. This method allows you to specify the columns on which you want to check for duplicates and remove them. Here’s an example:

Sub RemoveDuplicatesExample()
    Dim ws As Worksheet
    Dim rng As Range
    
    ' Set the worksheet and range to remove duplicates from
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set rng = ws.Range("A1:B10") ' Change the range to fit your data
    
    ' Remove duplicates in the specified range
    rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    
    ' xlYes assumes that the range has a header row
    ' If your range doesn't have a header row, change it to xlNo
End Sub
    

In this example, we have a worksheet named “Sheet1” and we want to remove duplicates from columns A and B in the range A1:B10. You can change the worksheet and range references as per your requirement.

The RemoveDuplicates method takes two parameters:

• Columns: This parameter specifies the columns on which you want to check for duplicates. You can provide multiple columns using the Array function. In the example, we pass Array(1, 2) to check for duplicates in columns 1 (A) and 2 (B).
• Header: This parameter specifies whether the range has a header row or not. If your range has a header row, use xlYes. Otherwise, use xlNo.

After running the RemoveDuplicatesExample macro, the duplicates in the specified range will be removed, and you will be left with only unique values.