It was part of the answer...

Dim allcells As Range
Set allcells = Range("B3,B6,B9,B12,B15,B18,B21,B24," _
& "C3,C6,C9,C12,C15,C18,C21,C24,D3,D6,D9,D12,D15,D18 ,D21,D24," _
& "E3,E6,E9,E12,E15,E18,E21,E24,F3,F6,F9,F12,F15,F18 ,F21,F24," _
& "G3,G6,G9,G12,G15,G18,G21,G24,H3,H6,H9,H12,H15,H18 ,H21,H24")

did work for me--but when I included the next line, it blew up really good.

You could do something like:

Dim allcells1 As Range
Dim allcells2 As Range
Dim allcells As Range

Set allcells1 = Range("B3,B6,B9,B12,B15,B18,B21,B24," _
& "C3,C6,C9,C12,C15,C18,C21,C24," _
& "D3,D6,D9,D12,D15,D18,D21,D24," _
& "E3,E6,E9,E12,E15,E18,E21,E24," _
& "F3,F6,F9,F12,F15,F18,F21,F24," _
& "G3,G6,G9,G12,G15,G18,G21,G24," _
& "H3,H6,H9,H12,H15,H18,H21,H24")

Set allcells2 = Range("I3,I6,I9,I12,I15,I18,I21,I24," _
& "J3,J6,J9,J12,J15,J18,J21,J24," _
& "K3,K6,K9,K12,K15,K18,K21,K24," _
& "L3,L6,L9,L12,L15,L18,L21,L24," _
& "M3,M6,M9,M12,M15,M18,M21")

Set allcells = Union(allcells1, allcells2)

But I think I'd do something like:

Dim allcells As Range
Set allcells _
= Intersect(Range("B:M"), Range("B3,B6,b9,b12,b14,b18,b21,b24").EntireRow)

wrote:

Hi Ingolf,
Thank you for taking the time to try and help me. Unfortunately that
wasn't the answer.

Simon

Ingolf wrote:
Hello Simon

Maybe it's as simple as this. There is a comma missing at the end of
each part of your range included in quotation marks, except the last
one, of course.

Ingolf


schrieb:

XL 2003. Win XP
I am using a version of Jon Walkenbach's code to produce a unique
collection that populates another workbook with the items in the
"NoDupes" list. My issue is with the first step setting the Range
allcells. My range is not large but not continuous either and due to
the nature of my workbook I want the No Dupes list to be in the order I
dictate. Here is the beginning of my code:

dim allcells as range

Set allcells = Range("B3,B6,B9,B12,B15,B18,B21,B24" _
& "C3,C6,C9,C12,C15,C18,C21,C24,D3,D6,D9,D12,D15,D18 ,D21,D24" _
& "E3,E6,E9,E12,E15,E18,E21,E24,F3,F6,F9,F12,F15,F18 ,F21,F24" _
& "G3,G6,G9,G12,G15,G18,G21,G24,H3,H6,H9,H12,H15,H18 ,H21,H24" _
& "I3,I6,I9,I12,I15,I18,I21,I24,J3,J6,J9,J12,J15,J18 ,J21,J24" _
& "K3,K6,K9,K12,K15,K18,K21,K24,L3,L6,L9,L12,L15,L18 ,L21,L24" _
& "M3,M6,M9,M12,M15,M18,M21")

When I run this I get a run-time error 1004, method range of
object_global failed.

I can break this range down into smaller chunks and the Set allcells
line works fine, which leads me to wonder if there is a limit I can set
the range?

I've tried to use a union statement to pull two separate ranges
together but I still got a error.

Interestingly if I reorder the range to Range ("B3:M3,B6:M6"... etc) I
have no problem.

Can anyone suggest how I might overcome this please and as a learning
step for me explain where I've gone wrong?

Many thanks for your help

Simon

--

Dave Peterson