get column letter

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

Code:

---

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

---

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

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

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.

Gibbo,

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

Code:

---

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

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

Gibbo

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]

Code:

---

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

---

:)

Quote:

---

Originally Posted by 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

Code:

---

Chr(ActiveCell.Column + 64)

---

---

What about double letters?

Code:

---

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

---

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.

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)

Code:

---

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

---

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

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

Code:

---

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

---

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

Code:

---

Range(StrData).Sort ..

---

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

Code:

---

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

---

Looks like you all got it nailed...

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

Code:

---

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

---

Brute force approach...
try:

Code:

---

            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

---

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

btw, welcome to the forum. :hi:

Quote:

---

Originally Posted by mancubus

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:

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.

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.

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

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

Mr Benson of Schenectady comes to VBAX?