Dim StartCell as Range
set StartCell = Range("B9")
range(StartCell,StartCell.End(xldown)).Name = "List1"


or

Sub Tester4()
Dim StartCell As Range
Set StartCell = Range("B9")
ThisWorkbook.Names.Add Name:="List1", RefersTo:= _
"=" & Range(StartCell, StartCell.End(xlDown)).Address(1, 1,
External:=True)
End Sub


--
Regards,
Tom Ogilvy




"Chuckles123" wrote in message
...

Dave,

Thanks for your help.

I used: Target.FormulaR1C1 = "=SUM(R3C:R[-1]C)",
which is very similar to your suggestion.

A corollary question -- I have a column of numbers (each cell contains
a number), with a fixed starting cell but a variable ending cell. I
want the macro to assign a Name (Workbook definition) to this range.
Any ideas?

Chuckles123


--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile:
http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=265814