Hi All

From what I know, Excel doesn't support multiple areas in named ranges
Does anyone know of any get arounds to this limitation from the fron
end or by using VBA.

Secondly, if however, programmatically, using VBA, I do create a name
range say "myRange" referring to say :-

Sheet1!A1:C50,Sheet2!A1:C50

with comma delimited multiple areas as shown above (at least 2 areas
and which happen to be on different worksheets of the same workbook
and then write a custom function to evaluate the contents of each o
the cells within this named range, is it possible to determine th
worksheet names of each of the areas of the multiple area named rang
using the following statement :-

firstAreaWorksheetName = myRange.Areas(1).Parent.Name
secondAreaWorksheetName = myRange.Areas(2).Parent.Name etc.

Or am I on the wrong track?

Any suggestions would be highly valued.


Best regards



Deepak Agarwa

--
Message posted from http://www.ExcelForum.com

You're on the wrong track. A range is a child object of a worksheet, so
a range cannot have areas in two different sheets. You could use a
collection, instead:

Dim colRanges As New Collection
Dim firstAreaWorksheetName As String
Dim secondAreaWorksheetName As String

colRanges.Add Sheets("Sheet1").Range("A1:C50").Cells
colRanges.Add Sheets("Sheet2").Range("A1:C50").Cells

firstAreaWorksheetName = colRanges(1).Parent.Name
secondAreaWorksheetName = colRanges(2).Parent.Name

MsgBox firstAreaWorksheetName & vbNewLine & secondAreaWorksheetName


In article ,
agarwaldvk wrote:

From what I know, Excel doesn't support multiple areas in named ranges.
Does anyone know of any get arounds to this limitation from the front
end or by using VBA.

Secondly, if however, programmatically, using VBA, I do create a named
range say "myRange" referring to say :-

Sheet1!A1:C50,Sheet2!A1:C50

with comma delimited multiple areas as shown above (at least 2 areas -
and which happen to be on different worksheets of the same workbook)
and then write a custom function to evaluate the contents of each of
the cells within this named range, is it possible to determine the
worksheet names of each of the areas of the multiple area named range
using the following statement :-

firstAreaWorksheetName = myRange.Areas(1).Parent.Name
secondAreaWorksheetName = myRange.Areas(2).Parent.Name etc.

Or am I on the wrong track?

Forgot to say that named ranges certainly can contain multiple areas
within one sheet:

Range("A1:J10,K11:T20").Name = "MyNamedRange"
MsgBox Range("MyNamedRange").Address


In article ,
agarwaldvk wrote:

From what I know, Excel doesn't support multiple areas in named ranges.
Does anyone know of any get arounds to this limitation from the front
end or by using VBA

JE

Thanks for clarifying matters for me! Greatly appreciated.


Best regards


Deepak Agarwa

--
Message posted from http://www.ExcelForum.com