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

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:[VBA]MsgBox Chr(ActiveCell.Column + 64)[/VBA]

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

btw, welcome to the forum.

[PMBottas]

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

btw, welcome to the forum.

yeaaaah, I know
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 ....

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

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