Get Column Letter of Active Cell in VBA

  • Thread starter Thread starter MIG
  • Start date Start date
M

MIG

For some reason, activecell.column returns a number, where as
range.select requires a string.

So it's 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.
 
For some reason, activecell.column returns a number, where as
range.select requires a string.

So it's 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.
Click to expand...

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
 
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
Click to expand...

Aha.

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

Thanks much.
 
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, athttp://www.dailydoseofexcel.com/archives/2004/05/21/column-numbers-to...

Best wishes Harald
Click to expand...

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.
 
For some reason, activecell.column returns a number, where as
range.select requires a string.

So it's 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.
Click to expand...

To get the letter(s) of the active cell column:

Sub ActiveCellColumnLetter()
MsgBox Split(ActiveCell.Address, "$")(1)
End Sub
 
For some reason, activecell.column returns a number, where as
range.select requires a string.

So it's 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.
Click to expand...

Function coletter(col As Integer) As String
arr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
If col > 26 Then
remainder = col Mod 26
devisor = (col - remainder) / 26
If remainder > 0 Then
coletter = Mid(arr, devisor, 1) & Mid(arr, remainder, 1)
Else
coletter = Mid(arr, devisor - 1, 1) & Mid(arr, 26, 1)
End If
Else
coletter = Mid(arr, col, 1)
End If

End Function
 
Function coletter(col As Integer) As String
arr = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
If col > 26 Then
remainder = col Mod 26
devisor = (col - remainder) / 26
If remainder > 0 Then
coletter = Mid(arr, devisor, 1) & Mid(arr, remainder, 1)
Else
coletter = Mid(arr, devisor - 1, 1) & Mid(arr, 26, 1)
End If
Else
coletter = Mid(arr, col, 1)
End If

End Function
Click to expand...

It's a lot simpler than that...

range(columns(5).address)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
To get the letter(s) of the active cell column:

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

Man, you made my day! This is the neatest solution!
 
It's a lot simpler than that...



range(columns(5).address)



--

Garry



Free usenet access at http://www.eternal-september.org

Classic VB Users Regroup!

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

Cheers Garry

From your suggestion i came up with this function. Will return only the column letter(s). Not sure if it's perfect but working for me.

Function ColToLetter(colNo As Variant) As String

ColToLetter = Mid(Columns(colNo).Address, 2, (InStr(2, Columns(colNo).Address, ":") - 2))

End Function
 
Cheers back at ya'!

Try this...

Function GetColLabel$(ColNum&)
Split(Replace(Columns(ColNum).Address,"$",""),":")(0)
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
For some reason, activecell.column returns a number, where as
range.select requires a string.

So it's 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.
Click to expand...

Here ya go
MyCol = Columns(ActiveCell.Column).Address
this will return a result of $A:$A or whatever column you have active.
 
This returns the column label for a specified column index...

Function GetColLabel$(ColNum&)
Split(Replace(Columns(ColNum).Address,"$",""),":")(0)
End Function


This returns the column range address for a specified column index...

Function GetColRangeAddr$(ColNum&)
Columns(ColNum).Address
End Function


This returns the column index of the 1st column index of a specified
range...

Function Get_FirstColNum&(Addr$)
Dim sLabel$
sLabel = Split(Split(Addr, ":")(0), "$")(1)
GetColNum = Columns(sLabel).Column
End Function


This returns the column index of the last column index of a specified
range...

Function Get_LastColNum&(Addr$)
Dim sLabel$
sLabel = Split(Split(Addr, ":")(1), "$")(1)
GetColNum = Columns(sLabel).Column
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top