Assign a Name to a Range in VBA

[Guest]

I would like to be able to assign a name to a range in VBA. For example:

Sub mac2()
Dim r As Range
Set r = Cells(1, 1)
For i = 2 To 10
If Not IsEmpty(Cells(i, i).Value) Then
Set r = Union(r, Cells(i, i))
End If
Next
r.Select

ActiveWorkbook.Names.Add Name:="diagonal", RefersToR1C1:= ?????????

End Sub


By Naming a range I can make it available to worksheet functions.

 

[Guest]

Sub mac2()
Dim r As Range
Dim i As Integer

Set r = Cells(1, 1)
For i = 2 To 10
If Not IsEmpty(Cells(i, i).Value) Then
Set r = Union(r, Cells(i, i))
End If
Next
r.Select

ActiveWorkbook.Names.Add Name:="diagonal", RefersTo:=r

End Sub

 

[papou]

Hello
ActiveWorkbook.Names.Add Name:="diagonal", RefersToR1C1:=r.Address

HTH
Cordially
Pascal

 

[Guest]

Thank you for your help. Your solution works perfectly for small ranges:

For i=2 to 10 worked
For i=2 to 224 worked
For i=1 to 225 threw a "1004" exception.

Is there a limit to the number of cells in a named range??

 

[Guest]

My guess: you're hitting a limit based on the number of characters in the
string that represents the addresses for all the cells.

 

[Dave Peterson]

Maybe just:

if r is nothing then
'error message here
else
r.name = "Diagonal"
end if