CODEWITHSUNDEEP
excelvba
NewUser342
NewUser342

Reputation: 9

How to remove duplicate rows in a spreadsheet

In column 'M' i have hundreds of rows with multiple duplicates. I only want one record to show per duplicate when i run my macro. Below is my code and it deletes all records apart from one.

Sub DeleteRows()

With ActiveSheet
    Set Rng = Range("M5:M").End(xlDown)
    Rng.RemoveDuplicates Columns:=1, Header:=xlYes
End With

End Sub

It starts from M5 as this is where the data is initially. Why would it only be showing one record?

Upvotes: 0

Views: 73

Answers (1)

BigBen
BigBen

Reputation: 50008

Your original attempt, Range("M5").End(xlDown), is just one cell.

Your new attempt, Range("M5:M").End(xlDown), is closer but not a valid Range reference.

Try the following:

Set Rng = Range("M5:M" & Cells(Rows.Count, "M").End(xlUp).Row)

EDIT:

If you're dealing with an entire range, you need to specify the Columns argument of Range.RemoveDuplicates, something like this:

Sub RemoveDupes()
    Dim lastRow As Long
    lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    Range("A5:V" & lastRow).RemoveDuplicates Columns:=Array(13), Header:=xlYes ' column M = 13
End Sub

Upvotes: 1

Related Questions