Extracting Column Letter

  • Thread starter Thread starter Rob
  • Start date Start date
R

Rob

Hello All,

I am trying to extract and use the column letter instead of the number. My
code looks something like this:

Dim StrLastColumn as String

strLastColumn = ActiveCell.Column

The problem with this is that the .Column property returns the number not
the letter. So, if I am in column Z I get 26 instead of Z. I really need Z.

Any thoughts?

Rob
 
Function GetCoLLet(ColNumber As Integer) As String
GetCoLLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

=getcollet(256) returns IV


Gord Dibben MS Excel MVP
 
You could do this...

strLastColumn = Left(ActiveCell.Address(True, False), _
InStr(ActiveCell.Address(True, False), "$") - 1)


Rick
 
This will do the same thing and is much more compact...

strLastColumn = Split(ActiveCell.Address(True, False), "$")(0)

Rick
 
If you ever need to do this in the worksheet:

=SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","")
 
XL2007 has over 16000 columns available, so your code would only return the
first two letters for Columns AAA through XFD.

Rick
 
strLastColumn = Split(ActiveCell.Address(True, False), "$")(0)
Click to expand...

You can omit the True and make it even more "compact" :-))

Split(ActiveCell.Address(, False), "$")(0)

or even:

Split(ActiveCell.Address(, 0), "$")(0)

--ron
 
Yeah, I know... but there are certain situations where I shy away from using
default values when posting code to newsgroups where readers may not be
familiar with some of the one-liner constructions I come up with... commas
next to opening parentheses is one of them.... too many people tend to type
code from postings rather than copy/paste it (which I have **never** been
able to understand) and the isolated comma next to an opening parentheses
(being an unusual character combination) seems too easy to over look to me.
Maybe I am being too overprotecting? And, of course, I could have compacted
my own response like so...

Split(ActiveCell.Address(1, 0), "$")(0)

if I had really thought about it (yes, plus 1 will work in place of True
even though the value of True is actually -1).<g>

Rick
 
if I had really thought about it (yes, plus 1 will work in place of True
even though the value of True is actually -1).<g>
Click to expand...

Yup -- any non-zero converts to TRUE.
--ron
 
if I had really thought about it (yes, plus 1 will work in place of True
Yup -- any non-zero converts to TRUE.
Click to expand...

In past postings (over in the compiled VB newsgroups), I explained it this
way... for evaluation of logical expressions, VB defines False to be zero
and True to be Not False.

Rick
 
Back
Top