i was using the below for a few days and it worked fine, all of a sudden this morning im getting "Application-defined r object defined error" I am trying to remove duplicates using column one in my "Assignments" tab. If it matters the data is in a table. But again whats odd is it was working fine 2 the other day and i do not believe anything has changed.

Worksheets("Assignments").Activate

ActiveSheet.UsedRange.RemoveDuplicates Columns:=1, Header:=xlYes

CodePudding user response：

I can reproduce the problem if I have a table on a worksheet and data outside the table. I put a table in A1:C4 and then filled cell G15 with data. When I ran RemoveDuplicates, I got the Application Defined error. I guess that means you can't remove duplicates on a range that includes a table unless the range and the table are exactly the same range.

I generally only use UsedRange if I have a specific reason to because it's a little unreliable. It's never smaller than you need, but sometimes it's bigger. If you want to remove dupes on a table, call out that table's range explicitly.

ActiveSheet.ListObjects("MyTable").Range.RemoveDuplicates 1, xlYes