Something small is getting me down, and I seem to be unable to find an
info source to help me.
I am defining a Range Name for a variable length list. As such I am
using xlDown to select the range. But then when I add the name, I want
to specify the selection I have just made, not a R1C1 selection.
The Help file describes these four options. However, I can not make one
of them work.
------------------------------------------------------------------------
--------------------------------------------------------
expression.Add(Name, RefersTo, Visible, MacroType, ShortcutKey,
Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1,
RefersToR1C1Local)
expression Required. An expression that returns a Names object.
RefersTo Optional Variant. Required unless one of the other RefersTo
arguments is specified. Describes what the name refers to (in the
language of the macro, using A1-style notation). Note Nothing is
returned if the reference does not exist.
RefersToLocal Optional Variant. Required unless one of the other
RefersTo arguments is specified. Describes what the name refers to (in
the language of the user, using A1-style notation).
RefersToR1C1 Optional Variant. Required unless one of the other
RefersTo arguments is specified. Describes what the name refers to (in
the language of the macro, using R1C1-style notation).
RefersToR1C1Local Optional Variant. Required unless one of the other
RefersTo arguments is specified. Describes what the name refers to (in
the language of the user, using R1C1-style notation).
------------------------------------------------------------------------
------------------------------------------------------
Sheets("Print_Sheet").Select
Range("A1").Select
If NameExists("Sheet_Name_List") = True Then
ActiveWorkbook.Names("Sheet_Name_List").Delete
Else
End If
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
'Here the issue lies. I want the relative reference I have just created
to be used.
'Option 1 - as recorded - absolute reference.
ActiveWorkbook.Names.Add Name:="Sheet_Name_List", RefersToR1C1:= _
"=Print_Sheet!R2C1:R37C1"
'Option 2 - relative reference
ActiveWorkbook.Names.Add Name:="Sheet_Name_List", RefersTo
Range.selection 'I need help to make this line work.