ActiveWorkbook.Names.Item(" YOUR HELP ").PLEASE

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
Right Guys, having a slight problem.

I need to name some cells to allow listing in a list via validation. I'll add a "=Unique" with the list properties of a cell, anyway.....I can get this to work be simply highlighting them and then naming them, but i would like to do this via VB, to allow for updates etc.

Firstly I had a problem as i couldn't overwrite or change what i had originally done.
I fixed that with this at the start of my code
Code:
ActiveWorkbook.Names.Item("Unique").Delete
BUT.... Even though the next VB selects the cells i want....
Code:
    Range("A2").Select
             Range(Selection, Selection.End(xlDown)).Select

To selected cells are not 'named', only the original cells are named and not the ones which have been added.
Here is my full code so far...

Code:
ActiveWorkbook.Names.Item("Unique").Delete

    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWorkbook.Names.Add Name:="Unique", RefersToR1C1:= _
        "='Glue Weights'!R2C1:R9C1"
I know this is likely to be something to do with Relative Cell Ref's etc, but either with Relative turned on or off... I can't sort it!!! :oops:
:LOL:
Could anyone help please?
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
sly

You aren't actually using the selection when trying to create the name.
Code:
Range(Range("A2", Range("A2.End(xlDown)).Name = "Unique"
 

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
Thanks Norie

This is the trouble with being self taught.... those little things which seem so obvious once you know them..... pass you by

Many Thanks
 

Grizlore

Active Member
Joined
Aug 22, 2006
Messages
259
sly

You aren't actually using the selection when trying to create the name.
Code:
Range(Range("A2", Range("A2.End(xlDown)).Name = "Unique"

My finished code is a follows

Code:
ActiveWorkbook.Names.Item("Unique").Delete
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Name = "Unique"

I couldnt get it to work exactly as you have written it, however MANY THANKS!!!!!!!
 

Forum statistics

Threads
1,141,155
Messages
5,704,626
Members
421,360
Latest member
Rhodia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top