Name a range in VBA (should be easy)

[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!

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

[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]

[QUOTE=Camlamb28;3605085]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
UsedRange.Name="rData0" ' Name new used range as rData0