RemoveDuplicates method doesn't work in Worksheet module [Excel 2010]

sektor · September 18th, 2009, 01:06 AM

I have a simple task - delete duplicates from range. Here's code:

Code:

Dim rng As Range
Set rng = Range("A1").CurrentRegion
rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

However, this code doesn't work in worksheet module, but it works in STANDARD module (of course with sheet prefixing)!!! Is there any explanation of such Excel's VBA behavior?

JP · September 18th, 2009, 09:43 AM

Is the code inside an event handler?

Note that according to MS online help, you can't remove duplicate values from data that is outlined or that has subtotals.

--JP

Quote:

Originally Posted by sektor

I have a simple task - delete duplicates from range. Here's code:

Code:

Dim rng As Range
Set rng = Range("A1").CurrentRegion
rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlNo

However, this code doesn't work in worksheet module, but it works in STANDARD module (of course with sheet prefixing)!!! Is there any explanation of such Excel's VBA behavior?

sektor · September 21st, 2009, 12:10 AM

No, code ain't in event handler, but in the worksheet module. I just make call from event handler. And my data isn't outlined and doesn't have subtotals.

The thing is that I just re-work code. In previous code I have event handler, which calls, say, Sub1. This Sub1, in its turn, calls Sub2 etc.
Thus, in my event handler I have only one line: Call Sub1. I tried to do the same thing in new workbook. All works fine. :(