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,

 

[Camlamb28]

In my experience usedrange is less error-prone.

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

Thanks, but not looking to include columns A:B in this case.

The code I used has worked for everything I've needed so far!

[COLOR=#574123]Range("C39").Select
[/COLOR][COLOR=#574123]Range(Selection, Selection.End(xlToRight)).Select
[/COLOR][COLOR=#574123]Selection.Name = "REPS"[/COLOR][COLOR=#574123]
[/COLOR]

 

[Camlamb28]

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)

Not sure what a dynamic named range is sorry... but yes it must be programmed for this case.

Got the problem sorted anyway thanks

 

[Smora]

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)

Complicate but works:
Range("B3:" & Left(Range("B3").End(xlToRight).Address(ColumnAbsolute:=False), InStr(Range("B3").End(xlToRight).Address(ColumnAbsolute:=False), "$") - 1) & Range("B3").End(xlDown).Row).Select

Of course you can replace '.Select' with '.Name = "REPS"'

Range("B3").CurrentRegion.Name = "REPS" will also works but you must be careful because it will consider any region where B3 belongs (ie it could select cells to the left and up from B3

Cheers

 

[Enigmarmalade]

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"

Generally, it is best to avoid using "Selection" programmatically. It is useful for interacting with the user (User to VBA or VBA to User) but if it is being used "within the code" there is almost always a better way to execute an action. In this case, this code can be shortened to:

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

 

[manilal]

In my experience usedrange is less error-prone.

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

UsedRange should work just fine.

UsedRange.ClearContents ' Clear data before pulling new data<code to="" pull="" data="">
'Code to pull data
UsedRange.Name="rData0" ' Name new used range as rData0

</code>

 

[TreeDude]

Alternatively,

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

I know this is 20 years old ... but the solution still works. I was trying to record something very similar and fiddle with it 'till it worked ... nada. This code is clean and easy.