How to remove duplicate rows in a spreadsheet

Question

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?

Note: You should avoid ActiveSheet and instead define which sheet you are referencing through it's CodeName. Then input a dot (.) in front of all references to be explicit, e.g: .Range(... — JvdV
– JvdV, Commented Oct 29, 2019 at 17:09

BigBen · Accepted Answer · 2019-10-29 16:12:52Z

1

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

edited Oct 29, 2019 at 16:12

answered Oct 29, 2019 at 15:59

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

NewUser342 Over a year ago

That removes all the duplicates in that columns which is good! But only column M has the duplicates removed i want the rows to be deleted aswell if that makes sense?

BigBen Over a year ago

Then you need to specify the Columns argument of Range.RemoveDuplicates, and it shouldn't just be column M as the range reference. What column does your data start in and what column does it end in?

BigBen Over a year ago

Debug.Print lastRow, is it what you want? Can't repro the error.

NewUser342 Over a year ago

I have merged cells above A5 to V5 which is causing the error. ' All merged cells need to be the same size'

NewUser342 Over a year ago

I removed them and still not working, this is frustrating

Collectives™ on Stack Overflow

How to remove duplicate rows in a spreadsheet

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related