Column Letter of Cell Address

[HunterN]

Hi,

How do I return a column letter of a cell address? Each time I
open the page there will be a different amount of columns that
have data in them.

The columns are contiguous.

Thanks much,
Nancy

 

[Datsmart]

This will list the Column Letter of the active cell.

MsgBox Chr(ActiveCell.Column + 64)

 

[Oaktree]

=MID(CELL("Address",A1),2,SEARCH("@",SUBSTITUTE(CELL("Address",A1),"$","@",2))-2)

Where A1 is the cell whose column letter you'd like to return.

John: I think the CHAR(64+) method fails with column AA or greater

 

[Datsmart]

Oaktree:
Thanks, for the pointer.

 

[Norie]

Nancy

Do you actually need the column letter?

 

[Scott Huish]

Perhaps:

=SUBSTITUTE(ADDRESS(1,COLUMN(A1),4),1,"")

 

[HunterN]

All of these examples are giving me an error.

myTestAdd = Substitute(Address(1, Column(A1), 4), 1, "")

Error is

Sub or Function not defined

Column is highlighted.

Thanks, Nancy

 

[Norie]

Nancy

Column and Address are worksheet functions.

Like I asked, do you really need the actual column letter?

 

[Richard Schollar]

Nancy

Can you tell us why you need the column letter? Is it because you wish to refer to that column later in your code?

Richard

 

[HunterN]

Richard,

Yes I do need to refer to it in the code later on.

Nancy