Run-time error 1004 Method "Range of object_global failed

[simon.q.rice]

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

 

[Ingolf]

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

 

[simon.q.rice]

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

Simon

 

[simon.q.rice]

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

Simon

 

[Dave Peterson]

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)

 

[simon.q.rice]

Hi Dave,
Thank you for your post and taking the time to help me out.

I tried both ways you suggested and each overcame the runtime error,
but I observed an interesting phenomenon with the order in which the
NoDupes collection was built. Using the Set allcells _
= Intersect(Range("B:M"),
Range("B3,B6,b9,b12,b14,b18,b21,b24").EntireRow) method the collection
built going across the range e.g. B3, C3, D3,E3,... M3, B6,C6,...M6
etc.

When I used the range union method the collection built up in a
completely different way but unfortunately not in the order B3,
B6,B9... etc I wanted.

Unless anyone else has any ideas I've resigned myself to changing the
way I use the NoDupes collection further down in my code.

Thank you once again.

Simon