Code Create Named Range from Selection , but can use or process on that range

[Chirag R Raval]

Dear Sir,

I want to create selection as named range to process & reference on that named range
below code not work properly.

Dim myRangeName As String 'Store Name of Range Name
Dim myrng as range 'for Store range object
        'specify defined name
                range(ActiveCell, ActiveCell.End(xlDown)).Select
                selection.Resize(, 4).Select
                myRangeName = "myrng"
        'create named range with workbook scope. Defined name is as specified. Cell range is the selection
 
    ActiveWorkbook.Names.add Name:=myrng, RefersTo:=selection
           
          range("myrng").Border
                            .LineStyle = xlContinuous
                            .Weight = xlThin
                            .ColorIndex = xlAutomatic
                            End With 'Selection No end
'                            End With 'active sheet no end

Error Shown On below line
'ActiveWorkbook.Names.add Name:=myrng, RefersTo:=selection"

Which Error Displayed?
Run Time error 1004
Application defined or Object defined error

may be ..I read somewhere (& also bear, suffered, faced trouble in macros as per below)
if I declare selection as range type variable , then that's you generate fault in VBA due to there are VBA rule that you can not use word "Selection" in your sub-function name or "Selection" could not be expression because "selection" keyword used by VBA it self , you can not use it that way..

I also & already used this statement selection.name="myrng" (as myrng variable type "String")
but it also fails & displayed
Run Time Error "91"
"Object Variable or "With Block Variable" & also various type of errors faced..

is there are matter of declare variable?
which become name & which become range object?
is there are must necessary to declare 2 variables, as 1 for name and other for range?
if there are something need to "set" keyword to use?
or there are need to must be "set" this range as object?

Is there are must be require fully qualified reference for work this code?

how to resolve? how can I create named range from selection (selection by code-not manually) & work on that named range or some process on that named range.

hope there are some rock solid solution to declare without fail selection of named range & how to successfully work on or reference that named range differently in your further process.

Regards,

Chirag Raval

 

[NARAYANK991]

Hi ,

Try this :

Public Sub TEMP()
          Const USERANGE = False
          Dim myRangeName As String 'Store Name of Range Name
          Dim myrng As Range 'for Store range object
          '    specify defined name
          Range(ActiveCell, ActiveCell.End(xlDown)).Select
          Set myrng = Selection.Resize(, 4)
          myRangeName = "myrng1"
          '    create named range with workbook scope. Defined name is as specified. Cell range is the selection

          ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myrng
         
          If USERANGE Then
              With myrng.Borders
                  .LineStyle = xlContinuous
                  .Weight = xlThin
                  .ColorIndex = xlAutomatic
              End With
          Else
              With myrng.Borders
                  .LineStyle = xlContinuous
                  .Weight = xlThin
                  .ColorIndex = xlAutomatic
              End With
          End If
End Sub

Narayan

 

[Chirag R Raval]

Dear Sir, @NARAYANK991 ,

Many thanks for your response,
but there are some descripance in code..that don't make a range.

if you select mannually that range, no names displayed in name manager
means code don't create named range..

you use myrng1 in your code but not used anywhere it in further..

you resize selection in process of seting myrng i already try to select full range after resize & set that full selection..but fails.

i already try to change RefersTo:=myrng as myrng1 but object or reference error displayed..

there are also can't understand use of "constant USERANGE=false"
is that like a constructed switch for on/off using this range or not
that also effact in name manager?

in short it not work

please help

Regards,
Chirag Raval

 

[NARAYANK991]

Hi ,

If you cannot read VBA code and understand it , I suggest you enroll for a VBA training course , or at the least start reading a book on VBA.

I don't think the code I posted differs so significantly from the code you posted that you cannot understand how it works.

Narayan

 

[Chirag R Raval]

Dear sir @NARAYANK991

I just know that if you want to
Many process on fixed range
Or selection , as many time as and when requirement, then if you memorised
That range to excel as giving some name to that range,
Then you can easily reference that range as many way and as many time anywhere..
Even you can use that range on between different application
Level process, can be use in multiple workbooks ,multiple sheets
As just call or reference that range's name in your currently calling
Procedure. You can even do many process ,as many time as your requirements on that named range Without take huge effortst to just make taget on it, every time when require that range.

I just want to make / create named range from uncertain & dynamic range selected by code which obtained from other website..that I try to modify & try to fit it with my requirement.

But for some my missing points i can not create named range explicitly which can be shown on name manager...that displayed proof of defiantly created named range . so I can use that range as require.

I already appriciate your modification on my obtained code...& also many times run that also but its may be my bad luck there..

I request you to please little check your code
You can see there are no named range in name manager..but surprisely its do bordered on target range..( but without create named range).

Further feel sorry if you found any hurtable words
In my previous post & re try to set my stepson your given code & will give feedback if I found sonething

Regards,

Chirag Raval

 

[p45cal]

(re)Create your named range in one shot:
Range(ActiveCell, ActiveCell.End(xlDown)).Resize(, 4).Name = "myRng"

 

[NARAYANK991]

Hi ,

There was a mistake in my earlier posted code ; see this :

Public Sub TEMP()
          Const USERANGE = True
          Dim myRangeName As String 'Store Name of Range Name
          Dim myrng As Range 'for Store range object
          '    specify defined name
          Range(ActiveCell, ActiveCell.End(xlDown)).Select
          Set myrng = Selection.Resize(, 4)
          myRangeName = "myrng1"
          '    create named range with workbook scope. Defined name is as specified. Cell range is the selection

          ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myrng
       
          If USERANGE Then
              With myrng.Borders
                  .LineStyle = xlContinuous
                  .Weight = xlThin
                  .ColorIndex = xlAutomatic
              End With
          Else
              With [myrng1].Borders
                  .LineStyle = xlContinuous
                  .Weight = xlThin
                  .ColorIndex = xlAutomatic
              End With
          End If
End Sub

If USERANGE is true , use is made of the VBA range variable myrng , while if USERANGE is put to False before the code is executed , use is made of the named range myrng1.

Narayan

 

[Chirag R Raval]

Dear Sir @NARAYANK991 & Sir @p45cal ,

Yes ...Amazing... Superb.. Rock Solid....as desired....
Your Both's code work like a charm..

Sir p45cal's instantly create named range as we can see it in
Name manager

Though if we want more on that for process
We must should create inviroment for trap it
In a steady form via fully qualified statements like this workbook.
To sheet reference, also required variables to store range & names
& for settings it we should use Set key wordswords.

Thank you sir p45 cal

Sir Narayan,
There are my mistake also that i dont mention that
I run code from personnel xlsb for want to apply it on
Any currently open & active workbook..

I changed.. This Workbook... to.. Active Workbook &
Result is clear..range created & displai in name manager.

Though user can just little know about Named Range, & not need it in past, or not fully awakend
About it,they don't realized about hidden power of Named range so they avoid to
Use it & there they make big mistake to avoid power of it like made by me till now.

Named range instantly present , when call it like horse come to his owner instantly on
Whisling by owner ....or in a movie Matrix..in one scene ,neo need weapons & just call
It, infinite number of racks presented with full of armour towards him
if user awakened & realized, Named range give same power to user in Excel..

This long post is for them , who in seek for it..

I request here to experts for give some notable useful link
That can focus on Named Range with examples of
How & where we can use it. Or what's is pros & cons.

Again many thanks to notable & respected experts on this site,
who's Invaluable efforts to solve problems of users.

Regards,

Chirag Raval