Error generating a list of unique values in range (XL2003)

[ker_01]

Hi all-

I am using the code below to generate a list of unique values from a large
worksheet; those unique values will then feed an autofilter so I can grab
each chunk of data for processing individually.

p=58751 'last row that contains data
Sheet2.Range("B1:B" & p).AdvancedFilter Action:=xlFilterCopy,
CopyToRange:=Sheet1.Range("D1"), Unique:=True

I've used similar code (different ranges) to generate unique lists in other
projects.

However, in this case I'm getting a 1004 error: "The extract range has a
missing or illegal field name."

Sheet2 cell B1 contains "displayname" and all the values underneath appear
to be regular strings (I didn't see any error values or anything like that-
this is a dump of our IT network bandwidth monitoring software).

Any suggestions?

Thanks,
Keith

 

[Per Jessen]

Hi Keith

The problem is that Advanced filter does not allow you to copy to another
sheet....

But if you use a named range as reference you can do it anyway:

Name cell D1 on sheet1 as DestRange, and use this:

....CopyToRange=Range("DestRangen") ....

Regards,
Per

 

[ker_01]

How very strange; when I change the destination range to D2, it works fine
even if there is already data there. It just didn't like the destination D1,
which I had previously used while testing and still had the column header
(first unique returned value) in it.

I think I'm good to go, but that made no sense at all.

Keith