I am trying to write a macro that uses Define Name to name a range of cells.
The range is always changing as rows and columns get added/deleted, so I
tried using 'Selection.End' functionality. However, when it gets to RefersTo
in Define Name, it hard codes the range instead of using the currently
selected range. Any thoughts? Thanks!

Sub Naming()
'
' Naming Macro
'

'
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add name:="Timeline", RefersToR1C1:= _
"=Timeline!R4C2:R49C66"
ActiveWorkbook.Names("Timeline").Comment = ""
Range("B2").Select
End Sub

If you are selecting a range, either manually or with VBA, just add a line
like:
Selection.Name="TheName"
"Kori" wrote in message
...
I am trying to write a macro that uses Define Name to name a range of
cells.
The range is always changing as rows and columns get added/deleted, so I
tried using 'Selection.End' functionality. However, when it gets to
RefersTo
in Define Name, it hard codes the range instead of using the currently
selected range. Any thoughts? Thanks!

Sub Naming()
'
' Naming Macro
'

'
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add name:="Timeline", RefersToR1C1:= _
"=Timeline!R4C2:R49C66"
ActiveWorkbook.Names("Timeline").Comment = ""
Range("B2").Select
End Sub

Per your criteria:

Application.Names.Add Name:="Timeline", _
RefersTo:=Range([b4], [b4].End(xlDown).End(xlToRight))

or

Application.Names.Add Name:="RangeName", _
RefersTo:=Range(Range("b4"), Range("b4").End(xlDown).End(xlToRight))


--
MacGuy


"Kori" wrote:

I am trying to write a macro that uses Define Name to name a range of cells.
The range is always changing as rows and columns get added/deleted, so I
tried using 'Selection.End' functionality. However, when it gets to RefersTo
in Define Name, it hard codes the range instead of using the currently
selected range. Any thoughts? Thanks!

Sub Naming()
'
' Naming Macro
'

'
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add name:="Timeline", RefersToR1C1:= _
"=Timeline!R4C2:R49C66"
ActiveWorkbook.Names("Timeline").Comment = ""
Range("B2").Select
End Sub

I forgot to add the .end(xl___) functionality works as long as there are no
blank cells in the first column (xlDown) and the last row (xlToRight)

Consider:

Application.Names.Add Name:="Timeline", _
RefersTo:=Range(Range("b4"), Range("b4").CurrentRegion)

or

Application.Names.Add Name:="Timeline", _
RefersTo:=Range([b4].Offset(ActiveSheet.UsedRange.Rows.Count - 1,
0), _
[b4].Offset(0, ActiveSheet.UsedRange.Columns.Count - 1))


--
MacGuy


"MacGuy" wrote:

Per your criteria:

Application.Names.Add Name:="Timeline", _
RefersTo:=Range([b4], [b4].End(xlDown).End(xlToRight))

or

Application.Names.Add Name:="RangeName", _
RefersTo:=Range(Range("b4"), Range("b4").End(xlDown).End(xlToRight))


--
MacGuy


"Kori" wrote:

I am trying to write a macro that uses Define Name to name a range of cells.
The range is always changing as rows and columns get added/deleted, so I
tried using 'Selection.End' functionality. However, when it gets to RefersTo
in Define Name, it hard codes the range instead of using the currently
selected range. Any thoughts? Thanks!

Sub Naming()
'
' Naming Macro
'

'
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add name:="Timeline", RefersToR1C1:= _
"=Timeline!R4C2:R49C66"
ActiveWorkbook.Names("Timeline").Comment = ""
Range("B2").Select
End Sub

Thank you!!!

"MacGuy" wrote:

I forgot to add the .end(xl___) functionality works as long as there are no
blank cells in the first column (xlDown) and the last row (xlToRight)

Consider:

Application.Names.Add Name:="Timeline", _
RefersTo:=Range(Range("b4"), Range("b4").CurrentRegion)

or

Application.Names.Add Name:="Timeline", _
RefersTo:=Range([b4].Offset(ActiveSheet.UsedRange.Rows.Count - 1,
0), _
[b4].Offset(0, ActiveSheet.UsedRange.Columns.Count - 1))


--
MacGuy


"MacGuy" wrote:

Per your criteria:

Application.Names.Add Name:="Timeline", _
RefersTo:=Range([b4], [b4].End(xlDown).End(xlToRight))

or

Application.Names.Add Name:="RangeName", _
RefersTo:=Range(Range("b4"), Range("b4").End(xlDown).End(xlToRight))


--
MacGuy


"Kori" wrote:

I am trying to write a macro that uses Define Name to name a range of cells.
The range is always changing as rows and columns get added/deleted, so I
tried using 'Selection.End' functionality. However, when it gets to RefersTo
in Define Name, it hard codes the range instead of using the currently
selected range. Any thoughts? Thanks!

Sub Naming()
'
' Naming Macro
'

'
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add name:="Timeline", RefersToR1C1:= _
"=Timeline!R4C2:R49C66"
ActiveWorkbook.Names("Timeline").Comment = ""
Range("B2").Select
End Sub

How about a nice one liner broken up for word wrap.

Sub namereg()
Range(("b4"), Range("b4").End(xlDown) _
.End(xlToRight)).Name = "Timeline"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Kori" wrote in message
...
I am trying to write a macro that uses Define Name to name a range of
cells.
The range is always changing as rows and columns get added/deleted, so I
tried using 'Selection.End' functionality. However, when it gets to
RefersTo
in Define Name, it hard codes the range instead of using the currently
selected range. Any thoughts? Thanks!

Sub Naming()
'
' Naming Macro
'

'
Range("B4").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add name:="Timeline", RefersToR1C1:= _
"=Timeline!R4C2:R49C66"
ActiveWorkbook.Names("Timeline").Comment = ""
Range("B2").Select
End Sub