Get Column Letter of Active Cell in VBA

[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.

 

[Don Guillett Excel MVP]

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.

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]

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

Aha.

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

Thanks much.

 

[Harald Staff]

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]

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.

===============
With ActiveCell
Range(Cells(2, .Column), Cells(10, .Column)).Select
End With
==================

 

[MIG]

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

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]

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.

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

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

 

[india.ankurchawla]

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.

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

 

[GS]

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

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

 

[tmcconnell78]

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

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

Man, you made my day! This is the neatest solution!

 

[GS]

Man, you made my day! This is the neatest solution!

Nice..!

--
Garry

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

 

[Ron Rosenfeld]

Man, you made my day! This is the neatest solution!

But your other question had to do with selecting a range based on the column number. There is no need for a string to do that and, depending on exactly what you want to do, you don't even need to know the column.
For example, if you want the cell that is five columns to the left of ActiveCell, then merely:

activecell(1,-5).Select will do that.

You will get an error if the cell is off the worksheet.

Finally, it is unusual to have a requirement to actually Select any particular cell or range in VBA. Most operations can be done on a cell without Selecting (or Activating) the cell.

 

[kingdt.dk]

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

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

 

[GS]

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

 

[drake6000]

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.

Here ya go
MyCol = Columns(ActiveCell.Column).Address
this will return a result of $A:$A or whatever column you have active.

 

[GS]

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