Excel VBA Delete Duplicate Rows

Question

I have an issue that seems so simple it is ridiculous but the duplicates are not deleting. I have the below code which should reference one row versus another and if all is the same between the rows I want the duplicates deleted. It isn't that the code errors out, it just doesn't delete the rows.

With Sheets("PR0Perf")
    lastrow = Cells(Rows.Count, "B").End(xlUp).Row
    Set rng = Range("A1:T" & lastrow)
    rng.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 _
    , 14, 15, 16, 17, 18, 19), Header:=xlYes
End With

It'as as if it is failing to highlight all of the cells on the sheet. Does anyone have any idea what the issue could be? I could paste the whole script but it is pages long.

YowE3K · Accepted Answer · 2017-06-21 00:17:58Z

3

Assuming you are trying to remove the duplicates from the worksheet named "PR0Perf", and not from the ActiveSheet, qualify your Range, Cells and Rows objects:

With Sheets("PR0Perf")
    lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
    Set rng = .Range("A1:T" & lastrow)
    rng.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13 _
    , 14, 15, 16, 17, 18, 19), Header:=xlYes
End With

Note: The code is only checking for duplicates in columns A:S. This means the value in column T will be the one applicable to the first row found for each unique combination in A:S.

edited Jun 21, 2017 at 0:17

answered Jun 20, 2017 at 23:06

YowE3K

24k7 gold badges28 silver badges45 bronze badges

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

3 Comments

Greg Herr Over a year ago

You are correct I am trying to remove all duplicates from the worksheet PR0Perf. I am not sure I fully understand what you are saying when you say qualify the range, cells and row objects. The number of rows happens to be dynamic each time the macro runs which is why I am using lastrow. Maybe I am over-complicating this and I could just run a macro to remove all duplicates from the entire sheet. Is that even possible?

Greg Herr Over a year ago

Turns out I am an idiot and was trying to remove duplicates on a filtered sheet. Once I did an if then to remove that filter it now works perfectly.

YowE3K Over a year ago

@GregHerr Qualifying the Range, etc, references is referring to the fact that (for example) Range("A1:T" & lastrow) is interpreted as ActiveSheet.Range("A1:T" & lastrow), i.e. it works on whichever sheet is active at the time. If you want to specifically do something to the "PR0Perf" sheet, you should use Sheets("PR0Perf").Range("A1:T" & lastrow), so that's the difference between the code in my answer and in the question. (And I make use of the fact that you have a With Sheets("PR0Perf") block which allows me to use the . shortcut instead of Sheets("PR0Perf"). several times.)

Greg Herr · Accepted Answer · 2017-06-21 13:44:20Z

0

I was trying to remove duplicates from a sheet that had a filter. Once I set the Macro to remove the filter prior to running the above code my problem went away. Thanks for the feedback team!

answered Jun 21, 2017 at 13:44

Greg Herr

371 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

Excel VBA Delete Duplicate Rows

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related