Delete duplicate rows based on multiple columns

Question

Bascially I have found the below formula which is perfect except it only filters duplicates out based on column A, whereas I only want the rows deleted if Col A, B and C are all duplicated.

Sub removeDupes()
    Dim i As Long
    Dim ws As Worksheet

    Set ws = ActiveSheet 'This can be changed to a specific sheet: Worksheets("sheetName")

    With ws

        For i = .Cells(.Rows.Count, 1).End(xlUp).Row To 3 Step -1
            If .Cells(i, 1).Value = .Cells(i + 1, 1).Value Then
                .Rows(i).Delete
            End If
        Next i
    End With
End Sub

How can I edit this code so it applies to 3 columns?

`If .Cells(i, 1).Value = .Cells(i + 1, 1).Value And .Cells(i, 2).Value = .Cells(i + 1, 2).Value And .Cells(i, 3).Value = .Cells(i + 1, 3).Value Then` This only works if the rows are directly one after the other. It would not search the whole sheet to see if the duplicate exists elsewhere. — Toddleson, Jul 08 '22 at 19:17
Why not: `ws.usedrange.removeduplicates columns:=Array(1,2,3)` ? — Scott Craner, Jul 08 '22 at 19:23
If there are many such duplicate cases, the code will be very slow if you delete each row at a time. There are some ways to optimize the code from this point of view. The code may become faster by 100 times. So, how many such duplicate occurrences do you estimate to exist? — FaneDuru, Jul 08 '22 at 19:24
@ScottCraner probably because they don't even understand `cells(r,c)`. — findwindow, Jul 08 '22 at 19:26
I knew that code looked familiar: https://stackoverflow.com/questions/45760644/trying-to-delete-rows-with-cells-that-have-same-value-as-previous-in-vba BTW if you look at the accepted answer(which deserved to be the accepted answer) you would see the removeDuplicates version. — Scott Craner, Jul 08 '22 at 19:32
@findwindow it was the comment line. That is something I put nearly every time when using `Set ws = ActiveSheet` Then it was just a matter of googling it. — Scott Craner, Jul 08 '22 at 19:35
@ScottCraner Is this your code?? :) It works perfect but only searches col A for duplicates. Col A is Name 1, Col B is Name 2, and Col 3 is Name 3, all of which belong to addresses. If 2 different people want something shipped to them at the same address, I need both of those lines but if both copies are going to the same person, I just need one line. In column O I've got consecutive numbers to see how many duplicates there are. For example: 1, 1, 2, 3, 1, 1, 2, 3, 4, 5, 1 and so on. so rows 2-4 go to the same address. — Marianne, Jul 08 '22 at 19:47

score 0 · Answer 1 · answered Jul 09 '22 at 06:42

Remove Duplicates

Before

After

Option Explicit

Sub RemoveDupesShort()
    
    With ActiveSheet.UsedRange
        .Range("A3", .Cells(.Rows.Count, .Columns.Count)) _
            .RemoveDuplicates (VBA.Array(1, 2, 3))
    End With
    
    MsgBox "Duplicates removed.", vbInformation
    
End Sub

Sub RemoveDupes()
    
    ' Define constants.
    Const FirstCellAddress As String = "A3"
    Dim DupeCols() As Variant: DupeCols = VBA.Array("A", "B", "C")
    
    ' Reference the worksheet ('ws').
    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    ' For worksheet 'Sheet1' in the workbook containing this code, instead use:
    'Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Reference the range ('rg').
    Dim rg As Range
    With ws.UsedRange
        Set rg = ws.Range(FirstCellAddress, .Cells(.Rows.Count, .Columns.Count))
    End With
    
    ' Write the column numbers to a zero-based variant array ('Cols').
    
    Dim cUpper As Long: cUpper = UBound(DupeCols)
    Dim Cols() As Variant: ReDim Cols(0 To cUpper)
    
    Dim c As Long
    
    For c = 0 To cUpper
        Cols(c) = ws.Columns(DupeCols(c)).Column
    Next c
    
    ' Remove duplicates.
    rg.RemoveDuplicates (Cols)
    ' Note that the array of column numbers also needs to be evaluated:
    ' '(Cols)' which is short for 'Evaluate(Cols)'
    
    ' Inform.
    MsgBox "Duplicates removed.", vbInformation
    
End Sub

I will try this tomorrow at work! I need it to only work on consecutive rows! — Marianne, Jul 10 '22 at 12:10

Delete duplicate rows based on multiple columns

1 Answers1

Remove Duplicates