Get Column Letter of Active Cell in VBA
 

On Thursday, 5 August 2010 04:37:23 UTC+4, James Ravenswood wrote:
On Jul 31, 12:31*pm, MIG wrote:
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

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

Get Column Letter of Active Cell in VBA
 

On Thursday, 5 August 2010 04:37:23 UTC+4, James Ravenswood wrote:
On Jul 31, 12:31*pm, MIG wrote:
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

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



---
This email is free from viruses and malware because avast! Antivirus protection is active.
http://www.avast.com

Get Column Letter of Active Cell in VBA
 

On Wed, 23 Oct 2013 05:08:01 -0700 (PDT), wrote:

On Thursday, 5 August 2010 04:37:23 UTC+4, James Ravenswood wrote:
On Jul 31, 12:31*pm, MIG wrote:
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

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.