Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges | Excel Worksheet Functions | |||
How do I copy a group of worksheets with named ranges in Excel 200 | Excel Worksheet Functions | |||
Named ranges | Excel Discussion (Misc queries) | |||
Max # of named ranges | Excel Discussion (Misc queries) | |||
Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) |