Excel VBA Get Column Letter

You can, with Excel VBA get column letter or letters in two basic ways.

The first would be...

Since a column can be from A to ZZ (A-Z and then AA to ZZ), you could divide the column number (2=B, 5=E, etc.) by 26, the 26 letters of the alphabet, and use the value and the remainder, by using the Mod function. Then basically adding 64 to equate 65 with the letter A. The ASCII value of A is 65, B is 66, and so on to Z.

The second way is easier...

you can do this by using the Address function of the Range object.

The Address function takes 5 parameters. We only need the first two, which are Row and Column absolute values. Absolute value means whether or not the value is preceded with a $ dollar sign.

In our example we'll use 45 which is column AS. In the Address function, by requesting that Row value be preceded by a $ sign, we can use it as an end marker for the column letter(s) like this...

AS$1 - so we'll take what is Left of the $ sign.

So, our Excel VBA get column letter example would be...

MyColumnLetter = Left(Cells(1, 45).Address(1, 0), InStr(1, Cells(1, 45).Address(1, 0), "$") - 1)  

This site is powered by Site Build It!. If you enjoy it, please check out the
Site Build It homepage
to learn more.

Return from Outlook VBA myMailItem Criteria to VBA Code Samples

Return to our Homepage