Convert column number to letter

This simple function converts a column number to a column letter. This is especially convenient when you are trying to convert say, the last column number on a worksheet to a letter for use in a conditional formula.

VBA Module Code

Sub Test_Col_Letter_Function()
    
    Dim i As Long, TestData As String
    For i = 1 To 35
        TestData = TestData & Col_Letter(i) & ", "
        If i = 19 Then TestData = TestData & vbLf
    Next i
    
    Debug.Print TestData

End Sub

Public Function Col_Letter(ColNum As Long) As String
Dim MyByte As Byte, MyLetter As String, StartCol As Long
StartCol = ColNum
    Do
        MyByte = ((ColNum - 1) Mod 26)
        MyLetter = Chr(MyByte + 65) & MyLetter
        ColNum = (ColNum - MyByte) \ 26
    Loop While ColNum > 0
    Col_Letter = MyLetter
    ColNum = StartCol
End Function

Let’s test it!

After pasting the code above into a VBA module, run the Sub Test_Col_Letter_Function and view the immediate window (ctrl + g) to see the results.

Tagged: