Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Absolute vs. relative range selection

  Asked By: Lu    Date: Jan 04    Category: MS Office    Views: 1143
  

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.

Thanks in advance

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Tommy Thompson     Answered On: Jan 04

Try this

ActiveWorkbook.Names.Add Name:="testname", RefersTo:=Range(Range("A2"),
Range("A2").End(xlDown))

 
Answer #2    Answered By: Adelmo Fischer     Answered On: Jan 04

These worked for me:

If you've already selected the range  on the sheet with xldown:

ActiveWorkbook.Names.Add Name:="Sheet_Name_List", RefersTo:=Selection


but you don't need to select  the whole range, just the topmost cell:

ActiveWorkbook.Names.Add Name:="Sheet_Name_List",
RefersTo:=Range(Selection, Selection.End(xlDown))

 
Didn't find what you were looking for? Find more on Absolute vs. relative range selection Or get search suggestion and latest updates.




Tagged: