Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of selected range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of selected range
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of selected range
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of selected range
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of selected range
On Jul 13, 12:09*pm, "Rick Rothstein \(MVP - VB\)" 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of selected range
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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of selected range
On Jul 13, 12:49*pm, Tom Ogilvy
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of selected range
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of selected range
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Address of selected range
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 "vandenberg p" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a range address with ADDRESS function? | Excel Worksheet Functions | |||
Lower right cell address in a selected range - Excel 2007 | Excel Programming | |||
Get selected cell name/address | Excel Programming | |||
How do I convert a selected Cell address in a Range to Values? | Excel Programming | |||
How do you get a Selected Range address into a variable? | Excel Programming |