Excel - Get Column Letter of Active Cell in VBA

Asked By MIG on 31-Jul-10 12:31 PM
For some reason, activecell.column returns a number, where as
range.select requires a string.

So it is not possible to, say, select a range based on returning the
current column, or is it?

Is there a way of either

a) returning the column letter or

b) selecting a range based on column number?

Thanks.




Don Guillett Excel MVP replied to MIG on 31-Jul-10 01:04 PM
columns(3).select
cells(1,3).entirecolumn.select
range("c1").entirecolumn.select

Sub whatcolletter()
With Range("c1")
MsgBox "The current column is " & Chr$(.Column + 64)
MsgBox "The last column is " & Chr$(.Column + .Columns.Count + 64)
End With
End Sub
MIG replied to Don Guillett Excel MVP on 31-Jul-10 01:14 PM
Aha.

So it looks like cells.select is what I want rather than range.select.

Thanks much.
Harald Staff replied to Don Guillett Excel MVP on 31-Jul-10 01:53 PM
Hi Don

The solution have problems beyond column Z.
We had a pretty fun thread summer 2004 when rumors said that future Excel
might have more than 256 columns, at
http://www.dailydoseofexcel.com/archives/2004/05/21/column-numbers-to-letters/

Best wishes Harald
Ron Rosenfeld replied to MIG on 31-Jul-10 07:56 PM
===============
With ActiveCell
Range(Cells(2, .Column), Cells(10, .Column)).Select
End With
==================
MIG replied to Harald Staff on 02-Aug-10 06:14 AM
Yes, that occurred to me too, but in my case resolved by selecting
based on number through cells.select instead of range.select.  Numbers
are much more intuitive than strings for this sort of thing.
James Ravenswood replied to MIG on 04-Aug-10 08:37 PM
To get the letter(s) of the active cell column:

Sub ActiveCellColumnLetter()
MsgBox Split(ActiveCell.Address, "$")(1)
End Sub