CODEWITHSUNDEEP
vbaexcel
Greg Herr
Greg Herr

Reputation: 37

Excel VBA Delete Duplicate Rows

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.

Upvotes: 0

Views: 3497

Answers (2)

Greg Herr
Greg Herr

Reputation: 37

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!

Upvotes: 0

YowE3K
YowE3K

Reputation: 23974

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.

Upvotes: 3

Related Questions