Assing a Name to a Range

[ryguy7272]

I am not having much luck with the following code:

Range("A65000").Select
Selection.End(xlDown).Select
Selection.End(xlUp).Select
ActiveCell.Offset(-1, 0).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Names.Add Name:="Region"

The range is A4:E17, but will almost certainly change in the near future.
It fails on the last line. What am I doing wrong?

I need the named range for a chart, which I am building on the fly:
Dim myChtObj As ChartObject
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=30, Width:=800, Top:=250, Height:=500)

myChtObj.Chart.SetSourceData
Source:=Sheets("Region-Chart").Range("Region")
myChtObj.Chart.ChartType = xlColumnClustered
'etc., etc., etc., etc., etc., etc., etc.,

Thanks,
Ryan---

 

[JLGWhiz]

You have to tell it where.

ActiveWorkbook.Names.Add Name:="Region", RefersTo:="=Sheet1!$A$4:$E$17"

 

[Rick Rothstein]

I think you can also just assign the name to the selection's Name
property...

Selection.Name = "Region"

However, if I read the OP's code correctly, I think he can use this code in
place of the code he posted...

With Cells(Rows.Count, 1).End(xlUp).CurrentRegion
.Resize(.Rows.Count - 1).Name = "Region"
End With

 

[ryguy7272]

Thanks Rick! That was exactly what I was looking for!! One more question...
How did you know to use With...End With? I've used it before, sometimes
copying/pasting other people's code, sometimes developing my own. How did
you identify the problem and know that the solution required With...End With?

Thanks again!!
Ryan---

 

[Rick Rothstein]

The With..End With is not really the solution... using the CurrentRegion and
assigning the name to the Name property is... the With..End With was a
convenience so I didn't have to repeat a long string of references. Note the
'dot' in front of the Resize and the Rows properties... that means both of
these properties refer back to the object of the With statement which
further means I did not have to type that object reference out (twice) once
for each of the properties. The With..End With block that I posted is
*exactly* equivalent to this single, one-line statement (which I'm sure you
newsreader will probably break up into what looks line two lines)...

Cells(Rows.Count, 1).End(xlUp).CurrentRegion.Resize(Cells(Rows.Count,
1).End(xlUp).CurrentRegion.Rows.Count - 1).Name = "Region"

Obviously, that looks ugly and is somewhat hard to follow... the With..End
With block allows the code to be presented in a more concise manner... that
is really all it does.

 

[ryguy7272]

Wow!! Again, I am amazed.
That's why you are an MVP.

Thanks for everything,
Ryan--