Excel Name object bug

[DM Unseen]

All,

I want to add a sheet name referencing a multi area range on another
sheet.

The normal code to do this actually fails:

Sub t()
Dim rngMyRange As Range
Dim shtMysheet As Worksheet
Dim shtMyOthersheet As Worksheet


Set shtMysheet = ActiveWorkbook.Worksheets(1)
Set shtMyOthersheet = ActiveWorkbook.Worksheets(2)
Set rngMyRange = shtMyOthersheet.Range("A1,A3")

shtMysheet.Names.Add "MyName", "=" & rngMyRange.Address(, , , True)
Debug.Print shtMysheet.Names("MyName").RefersTo
shtMysheet.Names("MyName").Delete
End Sub

Only the first area of the range is on the correct sheet. all other
areas are on the sheet of the defined name.

Anybode got some code to get around this?

DM Unseen

 

[DM Unseen]

Duh,

following could seems to work around this issue:

Dim rngArea as Range
DIm strAddress as string

If rngMyRange .Areas.Count > 1 Then
strAddress = vbNullString
For Each rngArea In rngNames.Areas
If Len(strAddress) > 0 Then strAddress = strAddress &
Application.International(xlListSeparator)
strAddress = strAddress & rngArea.Address(, , , True)
Next rngArea
Else
strAddress = rngNames.Address(, , , True)
End If

shtMysheet.Names.Add "MyName", "=" & strAddress

Dm Unseen

 

[Tom Ogilvy]

What does:

rngMyRange.Name = "MyName"

do?

 

[DM Unseen]

Thanks Tom,

That also works OK also, *but* it will only work within 1 workbook,
i.e. the created Named Range does not qualify the workbook name. My
solution will work *across* workbooks as well.
But right now I do not want/need that, so I'll use your suggestion
instead, which is a lot shorter& easier as well

Dm Unseen