get column letter

[gibbo1715]

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]

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]

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]

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]

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]

Thanks all i ll have a play tomorrow and post what i come up with

Gibbo

[Killian]

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]

Msgbox Chr(Range("AB1").Column + 64)

:)

[Bob Phillips]

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]

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]

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]

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]

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]

Hey gibbo, you know you don't need to select that, right?


Range(StrData).Sort ..

[johnske]

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]

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]

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]

you are 9 years late to post a solution to this thread. :devil2::rofl:

btw, welcome to the forum. :hi:

[PMBottas]

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]

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]

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]

MsgBox Split(ActiveCell.Address, "$")(1) works, since the assumption for address is xlA1

[SamT]

11-21-2005, 10:16 AM :)

[Bob Phillips]

Mr Benson of Schenectady comes to VBAX?