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.
--
Gary''s Student

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

--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

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.
--
Gary''s Student

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

HTH
Cordially
Pascal

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??
--
Gary's Student


"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

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.
--
Gary''s Student

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

"Gary''s Student" wrote:

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??
--
Gary's Student


"Jim Thomlinson" wrote:

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

--
HTH...

Jim Thomlinson


"Gary''s Student" wrote:

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.
--
Gary''s Student

Maybe just:

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



Gary''s Student wrote:

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.
--
Gary''s Student

--

Dave Peterson