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

--
RyGuy

You have to tell it where.

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

"ryguy7272" wrote:

> 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---
>
> --
> RyGuy

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

--
Rick (MVP - Excel)


"JLGWhiz" <(E-Mail Removed)> wrote in message
news2DFAB55-63F8-4D6C-B084-(E-Mail Removed)...
> You have to tell it where.
>
> ActiveWorkbook.Names.Add Name:="Region", RefersTo:="=Sheet1!$A$4:$E$17"
>
> "ryguy7272" wrote:
>
>> 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---
>>
>> --
>> RyGuy

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


--
RyGuy


"Rick Rothstein" wrote:

> 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
>
> --
> Rick (MVP - Excel)
>
>
> "JLGWhiz" <(E-Mail Removed)> wrote in message
> news2DFAB55-63F8-4D6C-B084-(E-Mail Removed)...
> > You have to tell it where.
> >
> > ActiveWorkbook.Names.Add Name:="Region", RefersTo:="=Sheet1!$A$4:$E$17"
> >
> > "ryguy7272" wrote:
> >
> >> 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---
> >>
> >> --
> >> RyGuy
>
>

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.

--
Rick (MVP - Excel)


"ryguy7272" <(E-Mail Removed)> wrote in message
news:B1A27CE6-F2DA-402C-9BDE-(E-Mail Removed)...
> 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---
>
>
> --
> RyGuy
>
>
> "Rick Rothstein" wrote:
>
>> 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
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "JLGWhiz" <(E-Mail Removed)> wrote in message
>> news2DFAB55-63F8-4D6C-B084-(E-Mail Removed)...
>> > You have to tell it where.
>> >
>> > ActiveWorkbook.Names.Add Name:="Region", RefersTo:="=Sheet1!$A$4:$E$17"
>> >
>> > "ryguy7272" wrote:
>> >
>> >> 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---
>> >>
>> >> --
>> >> RyGuy
>>
>>

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

Thanks for everything,
Ryan--

--
RyGuy


"Rick Rothstein" wrote:

> 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.
>
> --
> Rick (MVP - Excel)
>
>
> "ryguy7272" <(E-Mail Removed)> wrote in message
> news:B1A27CE6-F2DA-402C-9BDE-(E-Mail Removed)...
> > 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---
> >
> >
> > --
> > RyGuy
> >
> >
> > "Rick Rothstein" wrote:
> >
> >> 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
> >>
> >> --
> >> Rick (MVP - Excel)
> >>
> >>
> >> "JLGWhiz" <(E-Mail Removed)> wrote in message
> >> news2DFAB55-63F8-4D6C-B084-(E-Mail Removed)...
> >> > You have to tell it where.
> >> >
> >> > ActiveWorkbook.Names.Add Name:="Region", RefersTo:="=Sheet1!$A$4:$E$17"
> >> >
> >> > "ryguy7272" wrote:
> >> >
> >> >> 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---
> >> >>
> >> >> --
> >> >> RyGuy
> >>
> >>
>
>