R1C1 versus A1 in VB code
 

Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Column produces a
column NUMBER.

How do I get back instead, or convert that to, the letter portion of the
column label in A1 format?

e.g. the last active cell is "Z42"

The above code returns 26. I need it to return "Z".

R1C1 versus A1 in VB code
 

I do the following:

dim iColumn as integer, sColumn as String
iColumn=26
sColumn = Chr(iColumn + 64)

"Bill Sturdevant" wrote:

Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Column produces a
column NUMBER.

How do I get back instead, or convert that to, the letter portion of the
column label in A1 format?

e.g. the last active cell is "Z42"

The above code returns 26. I need it to return "Z".

R1C1 versus A1 in VB code
 

But, if the last column is "DJ", my code returns 114. The proposed solution
below does not handle that.

"Tony_VBACoder" wrote:

I do the following:

dim iColumn as integer, sColumn as String
iColumn=26
sColumn = Chr(iColumn + 64)

"Bill Sturdevant" wrote:

Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Column produces a
column NUMBER.

How do I get back instead, or convert that to, the letter portion of the
column label in A1 format?

e.g. the last active cell is "Z42"

The above code returns 26. I need it to return "Z".

R1C1 versus A1 in VB code
 

try temp = ActiveCell.Address to get you started

R1C1 versus A1 in VB code
 

Hi Bill,
If you use:
Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address (False,
False)
It will return: A1

If you use:
Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address
It will return: $A$1

HTH--Lonnie M.

R1C1 versus A1 in VB code
 

Thank you! We are almost there! What I am really trying to get is the "A"
in the "A1"!

"Lonnie M." wrote:

Hi Bill,
If you use:
Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address (False,
False)
It will return: A1

If you use:
Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address
It will return: $A$1

HTH--Lonnie M.

R1C1 versus A1 in VB code
 

Most suggestions in this thread wont work for columns beyond 26,
and you should make it a habit to try to make your code as universal
and bullitproof as possible. As you'll probably be using it more often,
wrap it in a function.

usage like
?debug.print
columnletter(activesheet.specialcells(xlCellTypeLa stcell).column)



'This one is "future proof",
'(if MS ever decides to allow more than 256 columns)
Function ColumnLetter(ByVal colNum As Long) As String
Do
ColumnLetter = Chr$(65 + (colNum - 1) Mod 26) & ColumnLetter
colNum = (colNum - 1) \ 26
Loop While colNum 0
End Function

'This one is marginally faster but limited to two letter combinations.
'(which works for all current versions).
Function Column_Letter(ByVal colNum As Long) As String
colNum = (colNum - 1) Mod 256
If colNum 25 Then Column_Letter = Chr$(64 + colNum \ 26)
Column_Letter = Column_Letter & Chr$(65 + colNum Mod 26)
End Function


hth


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bill Sturdevant wrote :

Thank you! We are almost there! What I am really trying to get is
the "A" in the "A1"!

"Lonnie M." wrote:

Hi Bill,
If you use:
Debug.Print
Selection.SpecialCells(xlCellTypeLastCell).Address (False, False)
It will return: A1

If you use:
Debug.Print Selection.SpecialCells(xlCellTypeLastCell).Address
It will return: $A$1

HTH--Lonnie M.

R1C1 versus A1 in VB code
 

Hi all, I found this post by Dana DeLouis--short, simple, and
brilliant--had to share it.

Dana DeLouis Aug 28 2001, 7:55 pm
Newsgroups: microsoft.public.excel.programming

Letter = Split(ActiveCell.Address, "$")(1)

Adapted to OP's example:
Debug.Print Split(Selection.SpecialCells(xlCellTypeLastCell).A ddress,
"$")(1)

R1C1 versus A1 in VB code
 

Lonnie, I know that elegant solution.
it may be brilliant, but my function column_letter is 10 times faster.


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Lonnie M. wrote :

Hi all, I found this post by Dana DeLouis--short, simple, and
brilliant--had to share it.

Dana DeLouis Aug 28 2001, 7:55 pm
Newsgroups: microsoft.public.excel.programming

Letter = Split(ActiveCell.Address, "$")(1)

Adapted to OP's example:
Debug.Print Split(Selection.SpecialCells(xlCellTypeLastCell).A ddress,
"$")(1)