Delete all duplicate rows in 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 

This is “Like rng.Cells (counter) -1,” which seems to be the reason - I get “Type Mismatch”.

Answer 1

There is a RemoveDuplicates method that you can 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 

Answer 2

Duplicate values ​​in any column can be removed with a simple loop cycle.

 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