12

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".

Improve this question
4
  • Try rng.Cells(counter - 1) Commented Jun 7, 2013 at 16:28
  • 1
    Have you tried going to Data -> Remove Duplicates? Or do you need to use VBA for this particular application? Commented Jun 7, 2013 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. Commented Jun 7, 2013 at 16:49
  • 1
    You have not accepted any of the answer for all the questions you have asked so far. Is there any reason ? Commented Jun 7, 2013 at 19:46

2 Answers 2

Reset to default
29

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
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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 :)
It also gets rid of the blank rows. Excellent!
How can I have it delete the whole row?
can i make Array(1, 2) as variable of array because it gives me an error wrong argument
@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
3

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
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.