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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 06:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com