Name a range in VBA (should be easy)

[WillR]

Ok... I have a query that refreshes into an excel worksheet and want to Name the output which will always be from cell A3 to cell D"something" (i.e. D 'end xldown??')

What would be the VB code to select the output and Name the whole datatable???

I'm sure this is an easy one.... for someone.

Thanks,

 

[k209310]

You select the range that you want
range("a1:A2").select

and then use range.name = "name" to name the range.

To name the whole application (is this what you want?) you can use

Application.Caption = "Name"

 

[dk]

Alternatively,

Range("A3:D" & Range("D3").End(xlDown).Row).Name = "DataTable"

 

[WillR]

On 2002-10-30 07:40, k209310 wrote:
You select the range that you want
range("a1:A2").select

and then use range.name = "name" to name the range.

To name the whole application (is this what you want?) you can use

Application.Caption = "Name"

I think you missed my point. The range will potentially change each time the data is imported. I wish to, using VB, not manually, firstly determine the extent of the range (i.e. A3:Dwhatever) and then name it, as part of the data refresh procedure.

I know i can use the following code...

Names.Add Name:="Groups", RefersTo:="

But its the refers to bit i'm trying to ascertain, .....

 

[WillR]

On 2002-10-30 07:44, dk wrote:
Alternatively,

Range("A3:D" & Range("D3").End(xlDown).Row).Name = "DataTable"

Ta Dan.... just the ticket!

 

[jobohm]

I often solve this with usedrange

SheetCodeName.usedrange

If I need to get around headers then try this :
SheetCodeName.usedrange.offset(1,1).Resize(sheetCodeNAme.usedrange.rows.count -1, SheetCodeName.usedrange.columns.count - 1)

If this range expands continously thats all you need, but usually it shrinks every now and then too. If so you'll need to .delete empty cells.
Perhaps even .EntireRow.delete to avoid selecting a lot of useless empty cells.

 

[Camlamb28]

Alternatively,

Range("A3:D" & Range("D3").End(xlDown).Row).Name = "DataTable"

Hey all,

This is exactly the thing I'm trying to do, except take a range which goes to the right instead of downwards.

I tried:

Range("C39:" & Range("C39").End(xlRight).Column & "39").Name = "REPS"

But came up with error 1004 (Application or object defined error)


Alternatively, I do have variables named in VBA for the number of rows across it will go if this is easier...


Cheers,
Cam

 

[Camlamb28]

Hey all,

This is exactly the thing I'm trying to do, except take a range which goes to the right instead of downwards.

I tried:

Range("C39:" & Range("C39").End(xlRight).Column & "39").Name = "REPS"

But came up with error 1004 (Application or object defined error)


Alternatively, I do have variables named in VBA for the number of rows across it will go if this is easier...


Cheers,
Cam

Just had a brainwave of how to do this... And it worked! For reference for anyone else the code I used selected the range and then proceeded to name it.

Here's the code:

Range("C39").Select
    Range(Selection, Selection.End(xlToRight)).Select
    
    Selection.Name = "REPS"

 

[jobohm]

Range("C39:" & Range("C39").End(xlRight).Column & "39").Name = "REPS"

In my experience usedrange is less error-prone.

UsedRange.Rows(39).Name = "REPS"

 

[nuked]

Must you do this programmatically? You could manually add a dynamic named-range:-

=OFFSET(Sheet1!$A$3,0,0,COUNTA(Sheet1!$A$3:$A$1048576),4)