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