Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Get Column Letter of Active Cell in VBA

On Saturday, July 31, 2010 9:31:10 AM UTC-7, 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.


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


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,514
Default Get Column Letter of Active Cell in VBA

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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Get Column Letter of Active Cell in VBA [email protected] Excel Discussion (Misc queries) 3 March 26th 14 05:57 AM
Get Column Letter of Active Cell in VBA [email protected] Excel Discussion (Misc queries) 2 October 23rd 13 05:35 PM
How to replace column letter in refferences with a function using the old column letter? Dmitry Kopnichev Links and Linking in Excel 6 October 13th 05 09:09 AM
How to replace column letter in refferences with a function using the old column letter? Dmitry Kopnichev Excel Worksheet Functions 6 October 13th 05 09:09 AM
column header changed from letter to number, how return to letter Ron Excel Discussion (Misc queries) 2 May 9th 05 08:34 PM


All times are GMT +1. The time now is 08:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"