I was trying to illustrate that you can make substitutions. If you execute
the code from the sheet where the name will go then you can just leave out
the sheet! part. Excel should fill it in for you, where appropriate.

Try changing my code instead of substituting yours.

Sub createdefinednamerange()
col1 = InputBox("enter 1st column letter")
col2 = InputBox("enter lastcol")
ms = "=offset($" & col1 & "$1,1,0,counta($" & col2 & ":$" & col2 &
")-1)"
ActiveWorkbook.Names.Add Name:="ser1", RefersTo:=ms
End Sub

Here is another idea where you could substitute inputbox for =1
Sub makename()
fr = 1
lr = 4
fc = 2
lc = 5
With Sheets("sheet6")
.Range(.Cells(fr, fc), .Cells(lr, lc)).Name = "Bill"
End With
End Sub

or even this but not dynamic so executed when needed
Sub makename()
With Sheets("sheet6")
fr = 1
lr = Application.CountA(.Columns(1))
fc = 2
lc = 5
.Range(.Cells(fr, fc), .Cells(lr, lc)).Name = "Billa"
End With
End Sub



--
Don Guillett
SalesAid Software

"Rosencrantz" wrote in message
oups.com...
Never mind, I got it to work-I left in lines from my own code in the
code you sent me, so it messed up the ranges. But now, when I call the
macro, the range becomes:

first =
OFFSET(Sheet1!$C:$C,1,0,COUNTA(grnd_results_d1_tex t_file.txt!$C:$C)-1)

Instead of

first = OFFSET(grnd_results_d1_text_file.txt!$C:$8, 1, 0, _
COUNTA(grnd_results_d1_text_file.txt!$C:$C)-1)

So, I'm trying to mess around with it to get it to be the second one.
If you have any ideas of how I can tweak the lines:

With ActiveWorkbook.names
.Add Name:=name1, RefersToR1C1:="=OFFSET(Sheet1!C" & first & _
",1,0,COUNTA(C" & first & ":C" & first & ")-1)"
.Add Name:=name2, RefersToR1C1:="=OFFSET(Sheet1!C" & second & _
",1,0,COUNTA(C" & second & ":C" & second & ")-1)"
End With

I would greatly appreciate it!

~M