Hi Steven,

Am Sun, 6 Oct 2013 19:59:03 -0700 (PDT) schrieb Steven North:

All the ranges are short 5 rows of data.

The lastrow is 802, headings start at row 9. Data starts at row 10.
All ranges are from rows 10 to 797 rather than 10 to 802.

if all ranges are from 10 to 797 you can use (Names are always the
headers):

Sub Test1()
Dim LCol As Integer
Dim Lrow As Long
Dim i As Integer

LCol = Cells(9, Columns.Count).End(xlToLeft).Column
Lrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LCol
ActiveWorkbook.Names.Add Cells(9, i), _
RefersTo:=Range(Cells(10, i), Cells(Lrow - 5, i))
Next
End Sub

If last row can change and you want 5 rows offset from the end then try:

Sub Test2()
Dim LCol As Integer
Dim Lrow As Long
Dim i As Integer

LCol = Cells(9, Columns.Count).End(xlToLeft).Column
Lrow = Cells(Rows.Count, 1).End(xlUp).Row

ActiveWorkbook.Names.Add Cells(9, 1), _
RefersTo:="=Offset($a$1,9,," & Lrow - 14 & ")"
For i = 2 To LCol
ActiveWorkbook.Names.Add Cells(9, i), _
RefersTo:="=Offset(" & Cells(9, 1) & ",," & i - 1 & ")"
Next
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2