Hi,

Instead of using AutoFilter why not use Advanced Filter with the copy to
another location option? You will need a criteria range and an output
range. Both must use titles that match those in the Data area. Take a look
at the help system. To keep it simple leave the copy to range on the same
sheet as the data.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"atmbonda" wrote in message
...

I have a large data set (90 000 rows) in Excel 2007 with duplicate rows.
I have filtered it down to 30 000 distinct rows.
When I tried to copy all those 30 000 distinct rows at once and paste it
into a different worksheet, I have an error message that reads "Microsoft
Excel cannot create or use the datarange reference because it's too
complex...."

If I copy 5-10 000 rowns at a time, it works but that will take me very
long
because I have many similar files to handle.

Please can someone assist?

Thank you


PS: I want to go from this ....

1st Column 2nd Column 3rd Column
Number 1 A OK
Number 1 A Duplicate
Number 1 A Duplicate
Number 2 B OK
Number 2 B Duplicate
Number 3 C OK
Number 4 D OK
Number 4 D Duplicate
Number 4 D Duplicate


To this...

1st Column 2nd Column
Number 1 A
Number 2 B
Number 3 C
Number 4 D