I have to play with lot of ranges in a worksheet. Say range1 is from
A1:A10, range2 is from B1:B10 and so on. I have 1000s of such ranges.

One of doing it is defining individial ranges say

dim range1 as range
dim range2 as range
.....
....
....

etc.

I dont want to use 1000 variables for 1000 different ranges. Is there
any way of creating an array of ranges? OR Whats the best way of
doing it?

Is this what you're after?

Set myRange = Union(myRange1, myRange2, myRange3)
--
HTH,
Barb Reinhardt



"Chaman" wrote:

I have to play with lot of ranges in a worksheet. Say range1 is from
A1:A10, range2 is from B1:B10 and so on. I have 1000s of such ranges.

One of doing it is defining individial ranges say

dim range1 as range
dim range2 as range
.....
....
....

etc.

I dont want to use 1000 variables for 1000 different ranges. Is there
any way of creating an array of ranges? OR Whats the best way of
doing it?

You can create a collection of ranges but as a guess you probably want to
create a single range object and just keep on re-defining it as is
necessary...

dim rngToSort as Range

set rngToSort = range("A1:A10")
rngToSort.sort ...
set rngToSort = rngToSort.offset(0, 1)
rngToSort.sort ...

--
HTH...

Jim Thomlinson


"Chaman" wrote:

I have to play with lot of ranges in a worksheet. Say range1 is from
A1:A10, range2 is from B1:B10 and so on. I have 1000s of such ranges.

One of doing it is defining individial ranges say

dim range1 as range
dim range2 as range
.....
....
....

etc.

I dont want to use 1000 variables for 1000 different ranges. Is there
any way of creating an array of ranges? OR Whats the best way of
doing it?

Barb, Thanks for your effort. Union will combine all my ranges and I
dont seek to do that. I just want to store the individual ranges
using a single variable.

Jim, Thanks to you too. I want to create a collection of ranges using
a single variable (say an array of ranges). I dont want to keep on re-
defining my single range variable again and again.

Can you guys help me now? I hope I'm able to explain it more clearly
now. Any help would be greatly appreciated.

On Aug 22, 12:50*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
You can create a collection of ranges but as a guess you probably want to
create a single range object and just keep on re-defining it as is
necessary...

dim rngToSort as Range

set rngToSort = range("A1:A10")
rngToSort.sort ...
set rngToSort = rngToSort.offset(0, 1)
rngToSort.sort ...

--
HTH...

Jim Thomlinson



"Chaman" wrote:
I have to play with lot of ranges in a worksheet. *Say range1 is from
A1:A10, range2 is from B1:B10 and so on. *I have 1000s of such ranges..

One of doing it is defining individial ranges say

dim range1 as range
dim range2 as range
.....
....
....

etc.

I dont want to use 1000 variables for 1000 different ranges. *Is there
any way of creating an array of ranges? *OR Whats the best way of
doing it?- Hide quoted text -

- Show quoted text -