Addressing a group of Named Ranges

[Mike Fogleman]

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

 

[Bernie Deitrick]

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

Even if they overlap, the code will work fine.

 

[Bernie Deitrick]

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

 

[Mike]

Thanks Guys, I guess a For...Next will have to do. BTW they do not overlap,
so that was not a concern.