Address of selected range

[vandenberg p]

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

 

[Gary Keramidas]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Tom Ogilvy]

set chrtrng = Range("J2").CurrentRegion

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

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

 

[Pieter Vandenberg]

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

 

[Pieter Vandenberg]

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

Hello: Gary:

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

 

[Pieter Vandenberg]

set chrtrng = Range("J2").CurrentRegion

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

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

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

 

[Rick Rothstein \(MVP - VB\)]

The Address property is a String, so you can't Set it to a Range

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

 

[Per Jessen]

Hi Pieter

Dim chrtrng as Range

not as String, then it will work.

Best regards,
Per

 

[Don Guillett]

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