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?
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,911
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!!!!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,111,596
Messages
5,541,150
Members
410,543
Latest member
ExcelGlenn
Top