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)

Return from Excel VBA Get Column Letter to VBA Code Samples

Return to our Homepage