Naming Ranges VBA

[kspence703]

Hi, new to the site.

Am trying to name ranges through VBA. I have four columns. In row A would be the name I want to assign to the four different ranges I want the name to be assigned to all of column A and all of Column B etc. through Column D. For example..Cell A1 is titled "Unit and as such I want the range [column A] to be named "Unit" and of course would want to be able to repeat this for all other columns, changing of course based on the name in the first cell of the column.

I am able to name the first column with the following: Range("A2:A1000000").Name = "Unit"....however when I go and try and name the second column I receive a run-time error.

Let me know if anyone can help.
Thanks!


KS

 

[KUYJS]

Dim Unit As Range
Set Unit = Thisworkbook.worksheets("Sheet1").Range("A2:A10000")

 

[MARK858]

Dim Unit As Range
Set Unit = Thisworkbook.worksheets("Sheet1").Range("A2:A10000")

@KUYJS, that is not a named range, it is a variable.

@kspence703, what is the runtime error number and description? and can you post the full code you used to name both ranges?

 

[Fluff]

Along with what Mark has said, what are the names you want to use for cols B:D?

 

[kspence703]

Units Sold - Column B
Price per Unit - Column C
Region - Column D

 

[Fluff]

You cannot have spaces in a named range

 

[Norie]

If the first row contains the names you could use CreateNames.

Range("A1:D1000").CreateNames Top:=True

Note, this will create named ranges with the same no of rows and any spaces in the names will be replaced with underscores.

Also note, if you converted the data to a table you could use structured references to refer to the columns by their heading