I have the following VBA code (it was recorded):

dim chrtrng as range

Range("j2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select


set chrtrng = selection.address
~~~~~~~

I now wish to know how to assign that range a name.
The above set does not work and I can't quite figure the
correct way to do this.


Thanks for any help.


Pieter

not sure if this is what you're looking for or not.

Sub test()
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Dim rng As Range

Set rng = ws.Range("J2")
With ws
.Range(rng, .Range(rng.End(xlDown), rng.End(xlToRight))).Name _
= "rangename"
End With
MsgBox Range("rangename").Address
End Sub

--


Gary


"vandenberg p" <(E-Mail Removed)> wrote in message
news:g5disa$lel$(E-Mail Removed)...
>I have the following VBA code (it was recorded):
>
> dim chrtrng as range
>
> Range("j2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
>
>
> set chrtrng = selection.address
> ~~~~~~~
>
> I now wish to know how to assign that range a name.
> The above set does not work and I can't quite figure the
> correct way to do this.
>
>
> Thanks for any help.
>
>
> Pieter

The Address property is a String, so you can't Set it to a Range object. I
guess technically you could do this...

Set chrrng = Range(Selection.Address)

but, since the Selection is already a Range, why not just do this...

Set chrrng = Selection

Rick


"vandenberg p" <(E-Mail Removed)> wrote in message
news:g5disa$lel$(E-Mail Removed)...
>I have the following VBA code (it was recorded):
>
> dim chrtrng as range
>
> Range("j2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
>
>
> set chrtrng = selection.address
> ~~~~~~~
>
> I now wish to know how to assign that range a name.
> The above set does not work and I can't quite figure the
> correct way to do this.
>
>
> Thanks for any help.
>
>
> Pieter

set chrtrng = Range("J2").CurrentRegion

if you want it to be a named range as in Insert=>Name=>Define

Range("J2").CurrentRegion.Name = "Data1"

--
Regards,
Tom Ogilvy


"vandenberg p" wrote:

> I have the following VBA code (it was recorded):
>
> dim chrtrng as range
>
> Range("j2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
>
>
> set chrtrng = selection.address
> ~~~~~~~
>
> I now wish to know how to assign that range a name.
> The above set does not work and I can't quite figure the
> correct way to do this.
>
>
> Thanks for any help.
>
>
> Pieter
>

On Jul 13, 12:09*pm, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.S...@NO.SPAMverizon.net> wrote:


> The Address property is a String, so you can't Set it to a Range object. I
> guess technically you could do this...
>
> Set chrrng = Range(Selection.Address)
>
> but, since the Selection is already a Range, why not just do this...
>
> Set chrrng = Selection
>
> Rick

Hello Rick:

Thank you for your reply. I tried the following:

Sub xprobchart()
sheetnam = "Distribution 10"
icount = 21
col = 10
Dim chrtrng2 As String
Sheets(sheetnam).Select
Range("j2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Set chrtrng2 = Selection
MsgBox " chrtrng " & chrtrng2

and got a compile error, objective required, for the line: set chrrng2
= selection

The first method you suggested worked just fine.

Thank you

Pieter

>
> "vandenberg p" <pavb...@rohan.sdsu.edu> wrote in message
>
> news:g5disa$lel$(E-Mail Removed)...
>
> >I have the following VBA code (it was recorded):
>
> > dim chrtrng as range
>


> > * *Range("j2").Select
> > * *Range(Selection, Selection.End(xlDown)).Select
> > * *Range(Selection, Selection.End(xlToRight)).Select
>
> > * set chrtrng = selection.address
> > ~~~~~~~
>
> > I now wish to know how to assign that range a name.
> > The above set does not work and I can't quite figure the
> > correct way to do this.
>
> > Thanks for any help.
>
> > Pieter

On Jul 13, 12:01*pm, "Gary Keramidas" <GKeramidasATmsn.com> wrote:
> not sure if this is what you're looking for or not.
>
> Sub test()
> Dim ws As Worksheet
> Set ws = Worksheets("Sheet1")
> Dim rng As Range
>
> Set rng = ws.Range("J2")
> * * With ws
> * * .Range(rng, .Range(rng.End(xlDown), rng.End(xlToRight))).Name _
> * * = "rangename"
> End With
> MsgBox Range("rangename").Address
> End Sub
>
> --
>
> Gary

Hello: Gary:

You suggestion worked perfectly. Along with the other posed suggestion
I now have two ways to do it.
Thank you.
Pieter

>
> "vandenberg p" <pavb...@rohan.sdsu.edu> wrote in message
>
> news:g5disa$lel$(E-Mail Removed)...
>
> >I have the following VBA code (it was recorded):
>
> > dim chrtrng as range
>
> > * *Range("j2").Select
> > * *Range(Selection, Selection.End(xlDown)).Select
> > * *Range(Selection, Selection.End(xlToRight)).Select
>
> > * set chrtrng = selection.address
> > ~~~~~~~
>
> > I now wish to know how to assign that range a name.
> > The above set does not work and I can't quite figure the
> > correct way to do this.
>
> > Thanks for any help.
>
> > Pieter

On Jul 13, 12:49*pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:
> set chrtrng = Range("J2").CurrentRegion
>
> if you want it to be a named range as in Insert=>Name=>Define
>
> Range("J2").CurrentRegion.Name = "Data1"
>
> --
> Regards,
> Tom Ogilvy

Tom:

I am suffering from an embarrassment of riches. I now have at least
three ways to do this. I appreciate the answer
and I am going to try to under the implications of all different
methods. Thank you.

Pieter


> "vandenberg p" wrote:
> > I have the following VBA code (it was recorded):
>
> > dim chrtrng as range
>
> > * * Range("j2").Select
> > * * Range(Selection, Selection.End(xlDown)).Select
> > * * Range(Selection, Selection.End(xlToRight)).Select
>
> > * *set chrtrng = selection.address
> > ~~~~~~~
>
> > I now wish to know how to assign that range a name.
> > The above set does not work and I can't quite figure the
> > correct way to do this.
>
> > Thanks for any help.
>
> > Pieter

> > The Address property is a String, so you can't Set it to a Range
>> object. I guess technically you could do this...
> >
> > Set chrrng = Range(Selection.Address)
> >
> > but, since the Selection is already a Range, why not just do this...
> >
> > Set chrrng = Selection

> Thank you for your reply. I tried the following:
>
> Sub xprobchart()
> sheetnam = "Distribution 10"
> icount = 21
> col = 10
> Dim chrtrng2 As String
> Sheets(sheetnam).Select
> Range("j2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
> Set chrtrng2 = Selection
> MsgBox " chrtrng " & chrtrng2

> and got a compile error, objective required, for the line:
> set chrrng2 = selection

The reason for your error is that you declared chrtrng2 as a String... you
cannot use Set to assign something to a String... it only works with
objects. Try declaring it this way...

Dim chrtrng2 As Range

then the Set statement will work; but you will have to change your MsgBox
statement to this...

MsgBox "chrtrng " & chrtrng2.Address

because chrtrng2 is now a Range, so you need to reference one of its
properties (I assumed Address) if you want to concatenate it with something.

Rick

Hi Pieter

Dim chrtrng as Range

not as String, then it will work.

Best regards,
Per


>Hello Rick:
>
>Thank you for your reply. I tried the following:
>
>Sub xprobchart()
>sheetnam = "Distribution 10"
>icount = 21
>col = 10
>Dim chrtrng2 As String
> Sheets(sheetnam).Select
> Range("j2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
> Set chrtrng2 = Selection
> MsgBox " chrtrng " & chrtrng2
>
>and got a compile error, objective required, for the line: set chrrng2
>= selection
>
>The first method you suggested worked just fine.
>
>Thank you
>
>Pieter
>

Another using your original criteria
Sub namerng()
Range("j2", Range("j2").End(xlDown).End(xlToRight)).Name = "hi"
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"vandenberg p" <(E-Mail Removed)> wrote in message
news:g5disa$lel$(E-Mail Removed)...
>I have the following VBA code (it was recorded):
>
> dim chrtrng as range
>
> Range("j2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Select
>
>
> set chrtrng = selection.address
> ~~~~~~~
>
> I now wish to know how to assign that range a name.
> The above set does not work and I can't quite figure the
> correct way to do this.
>
>
> Thanks for any help.
>
>
> Pieter