How To Remove Duplicates In Excel Using Vba

To remove duplicates in Excel using VBA, you can use the following code:

Sub RemoveDuplicates()
    Dim ws As Worksheet
    Dim rng As Range
    
    ' Set the worksheet object to the desired sheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
    
    ' Set the range object to the desired range
    Set rng = ws.Range("A1:A10") ' Change "A1:A10" to your range address
    
    ' Remove duplicates in the range
    rng.RemoveDuplicates Columns:=1, Header:=xlNo ' Change 1 to the column number of your range's column
    
    ' Clean up objects
    Set ws = Nothing
    Set rng = Nothing
End Sub
  

Let’s break down the code:

• Line 2: Declares variables for the worksheet and range objects.
• Line 5: Sets the worksheet object to the desired sheet. Replace “Sheet1” with the name of your sheet.
• Line 8: Sets the range object to the desired range containing the data. Replace “A1:A10” with the range address that covers your data.
• Line 11: Removes duplicates in the range specified by the “rng” variable. Change the “Columns” argument to the column number of the column you want to check for duplicates.
• Line 14-15: Cleans up the objects to release memory.

Here’s an example:

If you run the code on the above example, it will remove the duplicate entries in Column A: