5 Ways To Delete Duplicates In Excel Vba

Removing duplicate values in Excel is a common task that can be accomplished using various methods, including Excel VBA. In this article, we will explore five different ways to delete duplicates in Excel using VBA.

Understanding the Problem of Duplicate Values

Duplicate values can occur in any dataset, and they can be frustrating to deal with. Whether you're working with a small dataset or a large one, duplicate values can skew your analysis and make it difficult to get accurate results. In Excel, there are several ways to remove duplicate values, but using VBA can be particularly useful when working with large datasets.

Method 1: Using the Remove Duplicates Method

The Remove Duplicates method is a built-in VBA method that allows you to remove duplicate values from a dataset. This method is easy to use and can be applied to a range of cells. Here's an example of how to use the Remove Duplicates method:

Sub RemoveDuplicates()
    Range("A1:C10").RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End Sub

In this example, the code removes duplicate values from the range A1:C10, based on the values in columns 1, 2, and 3. The Header argument is set to xlYes, indicating that the first row of the range contains headers.

Using the Remove Duplicates Method with Multiple Columns

If you need to remove duplicates based on multiple columns, you can modify the code as follows:

Sub RemoveDuplicatesMultipleColumns()
    Range("A1:C10").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

In this example, the code removes duplicate values based on the values in columns 1 and 2.

Method 2: Using the For Loop Method

The For loop method is a more traditional approach to removing duplicates in Excel VBA. This method involves looping through each row of the dataset and checking for duplicate values. Here's an example of how to use the For loop method:

Sub RemoveDuplicatesForLoop()
    Dim i As Long, j As Long
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = lastRow To 2 Step -1
        For j = i - 1 To 2 Step -1
            If Cells(i, 1).Value = Cells(j, 1).Value And _
               Cells(i, 2).Value = Cells(j, 2).Value And _
               Cells(i, 3).Value = Cells(j, 3).Value Then
                Rows(i).Delete
                Exit For
            End If
        Next j
    Next i
End Sub

In this example, the code loops through each row of the dataset, starting from the bottom and working its way up. For each row, it checks for duplicate values in columns 1, 2, and 3. If a duplicate is found, the row is deleted.

Using the For Loop Method with Multiple Columns

If you need to remove duplicates based on multiple columns, you can modify the code as follows:

Sub RemoveDuplicatesForLoopMultipleColumns()
    Dim i As Long, j As Long
    Dim lastRow As Long
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = lastRow To 2 Step -1
        For j = i - 1 To 2 Step -1
            If Cells(i, 1).Value = Cells(j, 1).Value And Cells(i, 2).Value = Cells(j, 2).Value Then
                Rows(i).Delete
                Exit For
            End If
        Next j
    Next i
End Sub

In this example, the code removes duplicate values based on the values in columns 1 and 2.

Method 3: Using the Dictionary Object

The Dictionary object is a powerful tool in Excel VBA that allows you to store and retrieve data using keys. You can use the Dictionary object to remove duplicates by storing unique values in the dictionary and then deleting any duplicates. Here's an example of how to use the Dictionary object:

Sub RemoveDuplicatesDictionary()
    Dim dict As Object
    Dim i As Long, j As Long
    Dim lastRow As Long
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        If Not dict.Exists(Cells(i, 1).Value & "," & Cells(i, 2).Value & "," & Cells(i, 3).Value) Then
            dict.Add Cells(i, 1).Value & "," & Cells(i, 2).Value & "," & Cells(i, 3).Value, Nothing
        Else
            Rows(i).Delete
            i = i - 1
        End If
    Next i
    
    Set dict = Nothing
End Sub

In this example, the code uses the Dictionary object to store unique values in the dictionary. If a duplicate value is found, the row is deleted.

Using the Dictionary Object with Multiple Columns

If you need to remove duplicates based on multiple columns, you can modify the code as follows:

Sub RemoveDuplicatesDictionaryMultipleColumns()
    Dim dict As Object
    Dim i As Long, j As Long
    Dim lastRow As Long
    
    Set dict = CreateObject("Scripting.Dictionary")
    
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 2 To lastRow
        If Not dict.Exists(Cells(i, 1).Value & "," & Cells(i, 2).Value) Then
            dict.Add Cells(i, 1).Value & "," & Cells(i, 2).Value, Nothing
        Else
            Rows(i).Delete
            i = i - 1
        End If
    Next i
    
    Set dict = Nothing
End Sub

In this example, the code removes duplicate values based on the values in columns 1 and 2.

Method 4: Using the Filter Method

The Filter method is a built-in VBA method that allows you to filter a range of cells based on a specific criteria. You can use the Filter method to remove duplicates by filtering the range to exclude duplicates. Here's an example of how to use the Filter method:

Sub RemoveDuplicatesFilter()
    Range("A1:C10").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End Sub

In this example, the code filters the range A1:C10 to exclude duplicates.

Using the Filter Method with Multiple Columns

If you need to remove duplicates based on multiple columns, you can modify the code as follows:

Sub RemoveDuplicatesFilterMultipleColumns()
    Range("A1:C10").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("A1:B10"), Unique:=True
End Sub

In this example, the code filters the range A1:C10 to exclude duplicates based on the values in columns 1 and 2.

Method 5: Using the Power Query Method

The Power Query method is a built-in VBA method that allows you to create and manipulate data queries. You can use the Power Query method to remove duplicates by creating a query that excludes duplicates. Here's an example of how to use the Power Query method:

Sub RemoveDuplicatesPowerQuery()
    Dim qry As WorkbookQuery
    Set qry = ThisWorkbook.Queries.Add Name:="RemoveDuplicates", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "    #" & "Removed Duplicates" & " = Table.Distinct(Source)" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #" & "Removed Duplicates"
    qry.Load
End Sub

In this example, the code creates a query that excludes duplicates and loads the query into the workbook.

Using the Power Query Method with Multiple Columns

If you need to remove duplicates based on multiple columns, you can modify the code as follows:

Sub RemoveDuplicatesPowerQueryMultipleColumns()
    Dim qry As WorkbookQuery
    Set qry = ThisWorkbook.Queries.Add Name:="RemoveDuplicates", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "    #" & "Removed Duplicates" & " = Table.Distinct(Source, {""Column1"", ""Column2""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #" & "Removed Duplicates"
    qry.Load
End Sub

In this example, the code creates a query that excludes duplicates based on the values in columns 1 and 2 and loads the query into the workbook.

Remove Duplicates in Excel VBA Image Gallery

We hope this article has provided you with a comprehensive guide on how to remove duplicates in Excel VBA. Whether you're a beginner or an advanced user, we've covered five different methods that you can use to remove duplicates in your dataset. Don't forget to try out these methods and experiment with different techniques to find what works best for you. If you have any questions or need further assistance, feel free to ask in the comments section below.