Delete all duplicate rows Excel vba

Question

I have a worksheet with two columns: Date and Name. I want to delete all rows that are exact duplicates, leaving only unique values.

Here is my code (which doesn't work):

Sub DeleteRows()

Dim rng As Range
Dim counter As Long, numRows As Long

With ActiveSheet
    Set rng = ActiveSheet.Range("A1:B" & LastRowB)
End With
numRows = rng.Rows.Count

For counter = numRows To 1 Step -1
    If rng.Cells(counter) Like rng.Cells(counter) - 1 Then
        rng.Cells(counter).EntireRow.Delete
    End If
Next

End Sub

It's "Like rng.Cells(counter)-1" that seems to be the cause- I get "Type Mismatch".

Have you tried going to Data -> Remove Duplicates? Or do you need to use VBA for this particular application? — fbonetti, Jun 07 '13 at 16:32
The above coding is part of a procedure that does much more work on a fairly big worksheet, so I want to do the whole job with one click of a button. — , Jun 07 '13 at 16:49
You have not accepted any of the answer for all the questions you have asked so far. Is there any reason ? — Santosh, Jun 07 '13 at 19:46

score 30 · Accepted Answer · answered Jun 07 '13 at 16:40

30

There's a RemoveDuplicates method that you could use:

Sub DeleteRows()

    With ActiveSheet
        Set Rng = Range("A1", Range("B1").End(xlDown))
        Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
    End With

End Sub

answered Jun 07 '13 at 16:40

fbonetti

6,652
3
34
32

4

Great. Please click the check box to 'accept' this answer if you found it useful. It helps me rack up those sweet, sweet karma points :) – fbonetti Jun 07 '13 at 17:14
It also gets rid of the blank rows. Excellent! – CloseISQ Jan 25 '14 at 01:28
3

How can I have it delete the whole row? – mhmdshawqi May 05 '15 at 08:18
can i make `Array(1, 2)` as variable of array because it gives me an error wrong argument – shareef Mar 23 '16 at 13:16
@mhmdshawqi you could try taking a count of the entire table of data, then after remove duplicates, do another count, then use EntireRow.delete on the range – William Humphries May 20 '20 at 16:55

score 2 · Answer 2 · edited Apr 07 '17 at 09:07

2

The duplicate values in any column can be deleted with a simple for loop.

Sub remove()
Dim a As Long
For a = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If WorksheetFunction.CountIf(Range("A1:A" & a), Cells(a, 1)) > 1 Then Rows(a).Delete
Next
End Sub

edited Apr 07 '17 at 09:07

Martijn Pieters

1,048,767
296
4,058
3,343

answered Nov 23 '16 at 17:04

kadrleyn

364
1
5

Delete all duplicate rows Excel vba

2 Answers2

Linked