Associate a selected range of cells with a Named Range Object
Associate a selected range of cells with a Named Range Object
(OP)
In Excel VBA, how do you associate the currently selected cells to a named range object?
Dim myRange As Range
' Set myRange = Selection
' Set myRange = ActiveCell.CurrentRegion
neither of these seemed to work!
Dim myRange As Range
' Set myRange = Selection
' Set myRange = ActiveCell.CurrentRegion
neither of these seemed to work!





RE: Associate a selected range of cells with a Named Range Object
For Each one In Range("test")
MsgBox (one.Value)
Next one
Mutt
RE: Associate a selected range of cells with a Named Range Object
ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:=Selection
RE: Associate a selected range of cells with a Named Range Object
Sub test()
myrange = ActiveWindow.RangeSelection.Style
MsgBox (myrange)
myrange1 = ActiveWindow.RangeSelection.Value
For Each one In myrange1
MsgBox (one)
Next one
'Remark - can use myrange1=Selection - is identical to above as seems to store values only.
myrange2 = ActiveWindow.RangeSelection.Address
MsgBox (myrange2)
End Sub
RE: Associate a selected range of cells with a Named Range Object
Sub Test()
Dim myRange As Range
Set myRange = ActiveWindow.Selection
Debug.Print myRange.Address '(just for checking)
End Sub
It works over here...
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Associate a selected range of cells with a Named Range Object
Mutt,
I kept getting the following error with your approachs:
"Object variable or With block variable not set"
ab123456,
You nailed it on the head, I had tried something similar but did not have the syntax correct! Still learning this VBA notation
Thanks again
joerd,
Interestingly enough you approach worked using ActiveWindow instead of ActiveCell as I had it (saw the selected range in the debuger window), however, it did not seem to actually assign the range address to myRange. The reason I say that is that the code following this statement in my Macro using the range did not work with this approach but did with ab123456's.
Thanks again for all the help
RE: Associate a selected range of cells with a Named Range Object
I have found the best way to learn VBA code is to start the macro recorder going and then carry out the tasks and then examine the code and modify to suit your needs. Thats the way I found the code to solve your problem.
RE: Associate a selected range of cells with a Named Range Object
Regards
Mutt
RE: Associate a selected range of cells with a Named Range Object
If he has the "option explicit" checked in the VBE/options, he will see errors. Option Explicit means that all your variables have to be defined (i.e MyRange as Range, ...)
Other than that, I can't see any reason that it would give errors.
Also at the front of the sub (after defining the sub), add the following lines as needed:
Application.ScreenUpDating = False (this puts the operation behind the scene. Make sure to put it to true at the end of the code or you won't see anything).
Application.DisplayAlerts = False (this hides the error messages)
On Error Resume Next (This allows the sub to continue if an error has occurred).
These three (4 if you include putting the ScreenUpdating to true at the end of the sub) will enable your sub-routine to run faster and hopefully keep running as needed.