R1C1 versus A1 in VB code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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".
 
I do the following:

dim iColumn as integer, sColumn as String
iColumn=26
sColumn = Chr(iColumn + 64)
 
But, if the last column is "DJ", my code returns 114. The proposed solution
below does not handle that.
 
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.
 
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(xlCellTypeLastcell).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 :
 
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).Address,
"$")(1)
 
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 :
 
Back
Top