Multiple areas in named ranges

[agarwaldvk]

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

 

[JE McGimpsey]

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

 

[JE McGimpsey]

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

 

[agarwaldvk]

JE

Thanks for clarifying matters for me! Greatly appreciated.


Best regards


Deepak Agarwa