VB Code for Naming Ranges

K

krazylain

Can somehere tell me how to name a range in excel using VB code. Here'
how I get the range:

Range("L1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


As u can see the range changes, but I would like to create the sam
name for whatever the range is?

Thanks.
 
P

pauluk

the code you can use is

ActiveWorkbook.Names.Add Name:="YOURLabel"

so say your rang was L1:L33 you could have

Range("L1:L33").Select
ActiveWorkbook.Names.Add Name:="yourname"
RefersToR1C1:="=Sheet1!R1C12:R33C12"

After this when refering to that range it can be

Range("yourname")

Altnerativly you can do the following

dim your name as range
set yourname = range("L1:L33")

'refer to your range

yourname.Selec
 
D

Dana DeLouis

Here are two general Ideas. CurrentRegion works if the area is surrounded
by blank cells. On the other hand, "End" is a problem if you have blank
cells along the edge. Depends on how your data is set up.

Sub Demo()
With [L1].CurrentRegion
.Copy
.PasteSpecial Paste:=xlPasteValues
End With

'Or
With Range([L1], [L1].End(xlDown).End(xlToRight))
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End Sub
 
Top