[vba] Excel column number from column name

How to get the column number from column name in Excel using Excel macro?

Here's a pure VBA solution because Excel can hold joined cells:

Public Function GetIndexForColumn(Column As String) As Long
    Dim astrColumn()    As String
    Dim Result          As Long
    Dim i               As Integer
    Dim n               As Integer

    Column = UCase(Column)
    ReDim astrColumn(Len(Column) - 1)
    For i = 0 To (Len(Column) - 1)
        astrColumn(i) = Mid(Column, (i + 1), 1)
    Next
    n = 1
    For i = UBound(astrColumn) To 0 Step -1
        Result = (Result + ((Asc(astrColumn(i)) - 64) * n))
        n = (n * 26)
    Next
    GetIndexForColumn = Result
End Function

Basically, this function does the same as any Hex to Dec function, except that it only takes alphabetical chars (A = 1, B = 2, ...). The rightmost char counts single, each char to the left counts 26 times the char right to it (which makes AA = 27 [1 + 26], AAA = 703 [1 + 26 + 676]). The use of UCase() makes this function case-insensitive.

While you were looking for a VBA solution, this was my top result on google when looking for a formula solution, so I'll add this for anyone who came here for that like I did:

Excel formula to return the number from a column letter (From @A. Klomp's comment above), where cell A1 holds your column letter(s):

=column(indirect(A1&"1"))

As the indirect function is volatile, it recalculates whenever any cell is changed, so if you have a lot of these it could slow down your workbook. Consider another solution, such as the 'code' function, which gives you the number for an ASCII character, starting with 'A' at 65. Note that to do this you would need to check how many digits are in the column name, and alter the result depending on 'A', 'BB', or 'CCC'.

Excel formula to return the column letter from a number (From this previous question How to convert a column number (eg. 127) into an excel column (eg. AA), answered by @Ian), where A1 holds your column number:

=substitute(address(1,A1,4),"1","")

Note that both of these methods work regardless of how many letters are in the column name.

Hope this helps someone else.

You could skip all this and just put your data in a table. Then refer to the table and header and it will be completely dynamic. I know this is from 3 years ago but someone may still find this useful.

Example code:

Activesheet.Range("TableName[ColumnName]").Copy

You can also use :

activesheet.listobjects("TableName[ColumnName]").Copy

You can even use this reference system in worksheet formulas as well. Its very dynamic.

Hope this helps!

Based on Anastasiya's answer. I think this is the shortest vba command:

Option Explicit

Sub Sample()
    Dim sColumnLetter as String
    Dim iColumnNumber as Integer

    sColumnLetter = "C"
    iColumnNumber = Columns(sColumnLetter).Column

    MsgBox "The column number is " & iColumnNumber
End Sub

Caveat: The only condition for this code to work is that a worksheet is active, because Columns is equivalent to ActiveSheet.Columns. ;)

Write and run the following code in the Immediate Window

?cells(,"type the column name here").column

For example ?cells(,"BYL").column will return 2014. The code is case-insensitive, hence you may write ?cells(,"byl").column and the output will still be the same.

In my opinion the simpliest way to get column number is:
Sub Sample() ColName = ActiveCell.Column MsgBox ColName End Sub