Associate a selected range of cells with a Named Range Object 1

[dehnemi]

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!

 

[Mutt]

Hi - not sure if there is a way but you can use the Range object eg

For Each one In Range("test&quot
MsgBox (one.Value)
Next one

Mutt

 

[ab123456]

With the cells already selected try

ActiveWorkbook.Names.Add Name:="MyRange", RefersToR1C1:=Selection

 

[Mutt]

Sorry - misread - try this for some ideas :

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

 

[joerd]

If you want to set a range object to the selection, the following should work (although it seems that you have tried it already):

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.

 

[dehnemi]

Thanks everyone for the help!

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

 

[ab123456]

dehnemi,

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.

 

[Mutt]

Glad to see you got sorted out but find strange that you are getting error messages with my code and not seeing the range with Joerds as both ways worked fine in my version of Excel! I am using Excel 2000.

Regards
Mutt

 

[asmenut]

Mutt,

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.