Can I Assign a Name to a Column?

[G-fer]

Hi All ....

I was wondering if it's possible to assign a name to a column and then use the column name to select a cell, as in range(name,5).select ?

G-fer

 

[mikerickson]

Assuming that you have a Named Range in from the Define Names dialog box.
To select the cell in the fifth row of the named column colName, you could use the syntax
Range("colName").Cells(5, 1).Select
or
Range("colName").Range("A5").Select

A worksheet function for the same cell would be =INDEX(colName, 5, 1)

One difference between VB Cells and worksheet INDEX, is that if the 5, 1 is changed to a 5, 2 the VB won't error (it will return the 5th row of one column to the right of colName. INDEX will error.

 

[gregtx81]

another way...

dim colname as String
colname="A"

Range(colname & 5).Select

 

[G-fer]

Thanks Mike, Greg ....

That should fit the bill ... I was getting sick and tired of offsets.

BTW, will the column retain its name if another column is inserted before it, or will I need to cancel and then redefine the name?

G-fer

 

[mikerickson]

"will the column retain its name if another column is inserted before it, or will I need to cancel and then redefine the name?"

Yes, the column of cells will retain its name if another column is inserted before it.
If column D is named colDCells, and the user inserts a new column D, those cells that used to be in column D will still be named colDCells.