Column Letter to Column Number conversion

Phil11

New Member
Joined
Jul 8, 2005
Messages
48
Hi. Is there a VBA function which converts "H" to 8 and "AC" to 29, and in general, given any column letter label, returns the number of that column?

I'm thinking
Worksheets.("Sheet1").Columns("H").Index

but of course there's no such property. Please help. Thank you.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Phil

Why do you need to convert the number to the letter?

In code you can use the number.
Code:
Worksheets("Sheet1").Cells(1,8).EntireColumn.Select
 
Upvote 0
Hi Phil,

Like this:
Code:
Sub Test() 
    MsgBox ColLetter(Range("AA2").Column) 
End Sub 

Function ColLetter(iCol As Integer) As String 
'get column letter from the column number 

    Select Case iCol 
        Case 1 To 256 'OK 
            ColLetter = Left$(Columns(iCol).Address _ 
                (rowabsolute:=False, columnabsolute:=False), (iCol <= 26) + 2) 
            '(iCol <= 26) evaluates to either 0 (False) or -1 (True) 
        Case Else 'Wrong col ref 
            ColLetter = "Error : Invalid column number" 
    End Select 

End Function
HTH
 
Upvote 0
To get the column number from the column letter (you could use any valid row reference here, I'm using 1):

Msgbox Range("H1").Column

or with a variable:

Sub test()
Dim c As String
c = "H"
MsgBox Range(c & "1").Column
End Sub
 
Upvote 0
Sorry you want the number from the letter. Didn't catch that, folk normally want it the other way round.
:oops:
:oops:
:oops:
:oops:
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,109
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top