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?
Don Guillett Excel MVP replied to MIG on 31-Jul-10 01:04 PM
MsgBox "The current column is " & Chr$(.Column + 64)
MsgBox "The last column is " & Chr$(.Column + .Columns.Count + 64)
MIG replied to Don Guillett Excel MVP on 31-Jul-10 01:14 PM
So it looks like cells.select is what I want rather than range.select.
Harald Staff replied to Don Guillett Excel MVP on 31-Jul-10 01:53 PM
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
Best wishes Harald
Ron Rosenfeld replied to MIG on 31-Jul-10 07:56 PM
Range(Cells(2, .Column), Cells(10, .Column)).Select
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:
MsgBox Split(ActiveCell.Address, "$")(1)