PDA

View Full Version : [SOLVED] get column letter



gibbo1715
11-21-2005, 08:16 AM
Can anyone tell me if there is an easier way to get the column letter of the active cell than the method im using below

(Row is there just incase this method is of use to others, im only interested in the easiest method of getting the column letter)

cheers

gibbo


Sub Location()
Col = Split(ActiveCell(1).Address(1, 0), "$")(0)
'Row = Split(ActiveCell(1).Address(1, 0), "$")(1)
MsgBox Col' & Row
End Sub

mvidas
11-21-2005, 08:35 AM
Hi gibbo,

There really isn't an easier method (though you don't need the (1) after activecell), especially if you need the row number as well. But why do you need the column letter, as opposed to the column number (activecell.column)?

Matt

gibbo1715
11-21-2005, 08:45 AM
hi matt,

Was playing with something,

What I have is 10 ranges of information from row 4 to row 34 as follows (B4:C34) (D4:E34) and so on

What i want to do is first sort B4:C34 the D4:E34 and so on

started playing with looping across one after the other and as part of that got side tracked into how to get a column letter.

Still looking for the best method of my sorting problem but im guessing using named ranges might be best and then just looping through them

Unless your gonna (Which you probably are) tell me theres a better way:thumb

Thanks for the reply

Gibbo

shades
11-21-2005, 09:01 AM
Howdy. If I understand properly...if you use the "Cells" property, you wouldn't need the column letter, only the column number. Might make it easier to reference.

mvidas
11-21-2005, 09:09 AM
Gibbo,

I agree with shades, you can just use Cells and not even bother using the column letter.


Dim C As Long
For C = 2 To 20 Step 2
Range(Cells(4, C), Cells(34, C + 1)).Sort Key1:=Cells(4, C), Header:=xlNo
Next
Though named ranges is a good way to do it as well.
Matt

gibbo1715
11-21-2005, 01:11 PM
Thanks all i ll have a play tomorrow and post what i come up with

Gibbo

Killian
11-21-2005, 02:17 PM
If you really want the column letter, I think the best way is to use the column index property relative to the ASCII character code. Cap A is 65 so:MsgBox Chr(ActiveCell.Column + 64)

mvidas
11-21-2005, 02:19 PM
Msgbox Chr(Range("AB1").Column + 64)

:)

Bob Phillips
11-21-2005, 04:23 PM
If you really want the column letter, I think the best way is to use the column index property relative to the ASCII character code. Cap A is 65 so:MsgBox
Chr(ActiveCell.Column + 64)

What about double letters?


Function ColumnLetter(Col As Long)
Dim sColumn As String
On Error Resume Next
sColumn = Split(Columns(Col).Address(, False), ":")(1)
On Error GoTo 0
ColumnLetter = sColumn
End Function

Zack Barresse
11-21-2005, 06:06 PM
Split is good, but I suggest InStr. Should work in 97, Split will not.

And don't use Chr/ASCII.

And yes, use your row/absolute fashion.

johnske
11-21-2005, 06:28 PM
The one by gibbo at the top is probably the shortest one I've seen, but because of the use of split it only works for 2000 and up. Here's two by gibbo and killian (they both also give double letters such as AA, BC etc)


Sub ColumnName_gibbo()
'2000 and up
Dim Col As String
Col = Split(ActiveCell(1).Address(1, 0), "$")(0)
MsgBox Col
End Sub

Sub ColumnName_Killian()
'97 and up
Dim Name$
Name = ActiveCell.Address
MsgBox "Column selected is " & _
Left(Right(Name, Len(Name) - 1), _
InStr(Right(Name, Len(Name) - 1), "$") - 1)
End Sub

Killian
11-22-2005, 03:12 AM
Oh yeah, Excel... double letters... well I guess you can modify it for col index > 26 but Left and Instr is going to be more concise

gibbo1715
11-22-2005, 03:35 AM
Thanks for the replies, think my question was well and truly answered so marking as solved

Just in case your interested i chose to use a function in the end for my sorting as follows


Function Filter(StrData As String, StrFilter As String)
Range(StrData).Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range(StrFilter), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Function

Sub Filter1()
Filter "A2:B6", "B1"
Filter "C2:D6", "D1"
End Sub

Zack Barresse
11-22-2005, 09:19 AM
Hey gibbo, you know you don't need to select that, right?


Range(StrData).Sort ..

johnske
11-22-2005, 02:53 PM
While we're on the subject of improvements gibbo, what you have for 2000+ can be shortened even further by eliminating the variable Col to give


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

Aaron Blood
11-22-2005, 03:52 PM
Looks like you all got it nailed...

I've always just done it like this without the Split:


c = ActiveCell.EntireColumn.Address(False, False)
c = Left(c, InStr(1, c, ":") - 1)
'as for the row, it's always...
r = Activecell.Row

PMBottas
07-20-2014, 12:35 PM
Brute force approach...
try:


Public Function GetColumnAddr(ByVal cellAddr As String, ByVal keepAbs As Boolean) As String
Dim idx As Long
Dim str As String
Dim curChar As String

If cellAddr = "" Then
GetColumnAddr = ""
Exit Function
End If

idx = 1
str = "" ' not necessary but ...

If Left(cellAddr, 1) = "$" Then
idx = 2
if keepAbs then str = "$"
End If

curChar = Mid(cellAddr, idx, 1)
While (Not IsNumeric(curChar)) And (curChar <> "$")
str = str & curChar
idx = idx + 1
curChar = Mid(cellAddr, idx, 1)
Wend

GetColumnAddr = str

End Function

mancubus
07-20-2014, 01:41 PM
you are 9 years late to post a solution to this thread. :devil2::rofl:

btw, welcome to the forum. :hi:

PMBottas
07-20-2014, 04:43 PM
you are 9 years late to post a solution to this thread. :devil2::rofl:

btw, welcome to the forum. :hi:

yeaaaah, I know :doh:
but the problem itself doesn't get old.
esp. for a VBA newbie like me (a little late to the party)
Been program since 1978 under dozens of languages (literally) but in all that time I've never had to write any serious VBA.
Oh, I've ginned up my share of simple Excel macros through the years but nothing like what my employer's owner's son (who's one of the VPs) wants/needs me to do.
(I work in QC for a semiconductor equipment manufacturer)
I would have much rather preferred to write it in C#/VSTO but .... :mkay

... then watch.... after I get it all done, somebody up in the front office will say that's nice but we'd prefer if it were done in Google Docs
:banghead:

mancubus
07-21-2014, 04:12 AM
most of the users here (and in other ms office apps help forums) are not programmers but are experienced users with vba knowledge and skills.

so feel free to ask any questions in the forums. most of the time a member can provide a solution.

garilou
04-06-2015, 12:25 AM
No the problem doesn't get old. Your code just help me tremendously! Thank you.:clap:

I just used it for a complex dynamic name range creation sub and in case someone else would use it, I gave you the credit for that part of the code.

mrbillbenson
05-06-2017, 12:23 PM
MsgBox Split(ActiveCell.Address, "$")(1) works, since the assumption for address is xlA1

SamT
05-06-2017, 12:49 PM
11-21-2005, 10:16 AM :)

Bob Phillips
05-06-2017, 02:34 PM
Mr Benson of Schenectady comes to VBAX?