Addressing a group of Named Ranges
 

I have a number of named ranges all identical in size and shape. I want to
ClearContents of the same group of cells in each range. Can I address them
collectively?
eg:
Range("nr1, nr2, nr3, nr4, nr5").Range("B2:C6").ClearContents 'This does
not work

Mike

Addressing a group of Named Ranges
 

Mike,

As long as the named ranges don't overlap:

Dim myArea As Range
For Each myArea In Range("nr1, nr2, nr3, nr4, nr5").Areas
myArea.Range("B2:C6").ClearContents
Next myArea

Note that the B2:C6 is relative to the first cell of each named range.

HTH,
Bernie
MS Excel MVP

"Mike Fogleman" wrote in message
news:C%mqc.73503$iF6.6186157@attbi_s02...
I have a number of named ranges all identical in size and shape. I want to
ClearContents of the same group of cells in each range. Can I address them
collectively?
eg:
Range("nr1, nr2, nr3, nr4, nr5").Range("B2:C6").ClearContents 'This does
not work

Mike

Addressing a group of Named Ranges
 

I don't believe there is a way to address those sub areas all at once. A
workaround would be
Dim ar as Range
for each ar in Range("nr1,nr2,nr3,nr4,nr5).Areas
ar.Range("B2:C6").ClearContentns
Next

--
Regards,
Tom Ogilvy


"Mike Fogleman" wrote in message
news:C%mqc.73503$iF6.6186157@attbi_s02...
I have a number of named ranges all identical in size and shape. I want to
ClearContents of the same group of cells in each range. Can I address them
collectively?
eg:
Range("nr1, nr2, nr3, nr4, nr5").Range("B2:C6").ClearContents 'This does
not work

Mike

Addressing a group of Named Ranges
 

Even if they overlap, the code will work fine.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Mike,

As long as the named ranges don't overlap:

Dim myArea As Range
For Each myArea In Range("nr1, nr2, nr3, nr4, nr5").Areas
myArea.Range("B2:C6").ClearContents
Next myArea

Note that the B2:C6 is relative to the first cell of each named range.

HTH,
Bernie
MS Excel MVP

"Mike Fogleman" wrote in message
news:C%mqc.73503$iF6.6186157@attbi_s02...
I have a number of named ranges all identical in size and shape. I want
to
ClearContents of the same group of cells in each range. Can I address
them
collectively?
eg:
Range("nr1, nr2, nr3, nr4, nr5").Range("B2:C6").ClearContents 'This
does
not work

Mike

Addressing a group of Named Ranges
 

Tom,

True, true. I was more worried about the results than the code - I did test
with overlapping ranges and thought some concerns might be raised since more
than B2:C6 of some ranges were being cleared...

Bernie
MS Excel MVP

"Tom Ogilvy" wrote in message
...
Even if they overlap, the code will work fine.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Mike,

As long as the named ranges don't overlap:

Dim myArea As Range
For Each myArea In Range("nr1, nr2, nr3, nr4, nr5").Areas
myArea.Range("B2:C6").ClearContents
Next myArea

Note that the B2:C6 is relative to the first cell of each named range.

HTH,
Bernie
MS Excel MVP

"Mike Fogleman" wrote in message
news:C%mqc.73503$iF6.6186157@attbi_s02...
I have a number of named ranges all identical in size and shape. I
want
to
ClearContents of the same group of cells in each range. Can I address
them
collectively?
eg:
Range("nr1, nr2, nr3, nr4, nr5").Range("B2:C6").ClearContents 'This
does
not work

Mike

Addressing a group of Named Ranges
 

Thanks Guys, I guess a For...Next will have to do. BTW they do not overlap,
so that was not a concern.
Tom Ogilvy wrote in message
...
I don't believe there is a way to address those sub areas all at once. A
workaround would be
Dim ar as Range
for each ar in Range("nr1,nr2,nr3,nr4,nr5).Areas
ar.Range("B2:C6").ClearContentns
Next

--
Regards,
Tom Ogilvy


"Mike Fogleman" wrote in message
news:C%mqc.73503$iF6.6186157@attbi_s02...
I have a number of named ranges all identical in size and shape. I want
to
ClearContents of the same group of cells in each range. Can I address
them
collectively?
eg:
Range("nr1, nr2, nr3, nr4, nr5").Range("B2:C6").ClearContents 'This
does
not work

Mike