Extracting Column Letter

[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

 

[Gord Dibben]

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

 

[Rick Rothstein \(MVP - VB\)]

You could do this...

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


Rick

 

[Rick Rothstein \(MVP - VB\)]

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

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

Rick

 

[Gary''s Student]

If you ever need to do this in the worksheet:

=SUBSTITUTE((LEFT(ADDRESS(1,COLUMN()),3)),"$","")

 

[Rick Rothstein \(MVP - VB\)]

Provided he isn't using XL2007, of course.<g>

Rick

 

[Gary''s Student]

Why not 2007??
--
Gary''s Student - gsnu200786

Provided he isn't using XL2007, of course.<g>

Rick

 

[Rick Rothstein \(MVP - VB\)]

XL2007 has over 16000 columns available, so your code would only return the
first two letters for Columns AAA through XFD.

Rick

 

[Dave Peterson]

But something like this should work:

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

 

[Rick Rothstein \(MVP - VB\)]

Yep! That will work fine.

Rick

But something like this should work:

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

 

[Ron Rosenfeld]

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

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

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

or even:

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

--ron

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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>

Yup -- any non-zero converts to TRUE.
--ron

 

[Rick Rothstein \(MVP - VB\)]

if I had really thought about it (yes, plus 1 will work in place of True

Yup -- any non-zero converts to TRUE.

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