Table of Contents
The VBA code to convert a column number to its corresponding letter is the Columns property of the Range object. The syntax is “Range(“A1″).Columns(column_number).Column”. The column number should be replaced with the numeric value of the column, and the resulting value will be the letter representation of the column. For example, Range(“A1”).Columns(3).Column would return “C”.
You can use the following syntax in VBA to convert a column number to a letter:
Sub ConvertNumberToLetter()
Range("B2") = Split((Columns(Range("A2")).Address(, 0)), ":")(0)
End Sub
This particular macro will convert the column number in cell A2 to a letter and display the letter in cell B2.
For example, if the value in cell A2 is 4 then cell B2 will display “D” since this is the letter that corresponds to the fourth column in Excel.
The following example shows how to use this syntax in practice.
Example: Convert Column Number to Letter in VBA
Suppose we would like to know what column letter corresponds to a column number of 4 in Excel:
We can create the following macro to convert this number to a letter:
Sub ConvertNumberToLetter()
Range("B2") = Split((Columns(Range("A2")).Address(, 0)), ":")(0)
End Sub
When we run this macro, we receive the following output:
Cell B2 displays a value of “D” since this is the letter that corresponds to the fourth column.
If we change the number in cell A2 and run the macro again, then cell B2 will display the updated column letter.
For example, suppose we change the number in cell A2 to 73 and run the macro again:
Cell B2 now displays a value of “BU” since this is the column letter that corresponds to the 73rd column.