This code works okay. I found and modified the original to do a single named range by selecting the cell with the "Name_to-be" in it and run the code.

It produces a Refers To: offset formula to make the range dynamic like this, where the cell selected was H1 and with a name in H1.

=OFFSET(Sheet1!$H$2,0,0,COUNTA(Sheet1!$H:$H)-1,1)

All that is fine.

Howerer, if the selected cell and name is in cell H5, the code gives up the same offset formula instead of refering to H6 and on down.

I tried to incorporat the sRow value into the formula in place of "R2C" but failed at that. It would also need to change the COUNTA formula part to "Sheet1!$H6:$H???)-1,1)" or whatever.

At a loss to get that done.

Thanks,
Howard


Sub DynamicNameMaker()

Dim Col As Long
Dim sName As String
Dim Sht As String
Dim sRow As Long

'**Select the cell that will be the range name and header location

'grab sheet name
Sht = "'" & ActiveSheet.Name & "'"

With Selection

Col = ActiveCell.Column 'c.Column
sName = ActiveCell.Value
sRow = ActiveCell.Offset(1, 0).Row

If Len(sName) 1 Then
'replace spaces with underscores
sName = Replace(sName, " ", "_", 1)

MsgBox "The named range name will appear as" _
& vbCr & vbCr & " " & sName _
& vbCr & vbCr & "in the Name Manager."

'create the name
ActiveWorkbook.Names.Add Name:=sName, RefersToR1C1:= _
"=OFFSET(" & Sht & "!R2C" & Col & ",0,0,COUNTA(" & Sht & "!C" & Col & ")-1,1)"

End If

End With
End Sub