Use this VBA function to convert a column number to a letter. This isn’t often needed, but it can be useful if you want to build a range in your macro and you’re more comfortable doing so with the A1 notation, like B2:D5, instead of row/column notation.


VBA Column Number to Letter

Public Function ColumnLetter(ColumnNumber As Long) As String
ColumnLetter = Split(Cells(1, ColumnNumber).Address(True, False), "$")(0)
End Function

Did you find this helpful?

Please support wellsr.com by purchasing an add-in below. Your support helps me continue to post VBA tutorials like this one.

$15

Mouse to Macro

Record your mouse clicks and cursor movements and convert them directly into VBA macros so you can automate your mouse.

$50

wellsrPRO

Import hundreds of macro examples from wellsr.com without ever leaving Excel and organize your personal macro library with this best-selling add-in.

$50

CF Shapes

Build stunning dashboards by dynamically controlling your shapes with this add-in that enables conditional formatting for shapes.


How to use the ColumnLetter Function

To use this function, simply pass it an argument with the number of the column you want to convert to an Excel letter. Let’s look at a few examples:

Sub TestFunction()
Dim str1 As String
str1 = ColumnLetter(10) 'Returns J
str1 = ColumnLetter(3)  'Returns C
str1 = ColumnLetter(50) 'Returns AX
End Sub

Real Life VBA Example

Here’s a real life example of where you might find it useful to return the column letter associated with a corresponding column number.

Sub LastColumnExample()
Dim lastrow As Long
Dim lastcol As Long
Dim lastcolA As String

'Find last row and last column
lastrow = Range("A" & Rows.Count).End(xlUp).Row
lastcol = Range("A1").End(xlToRight).Column 'returns column number
lastcolA = ColumnLetter(lastcol) 'Convert column number to letter

'Copy contents from Sheet1 to Sheet 2
Sheets("Sheet2").Range("A1:" & lastcolA & lastrow) = Sheets("Sheet1").Range("A1:" & lastcolA & lastrow).Value
End Sub

You might find this to be a silly example. I know you can build a range many different ways, but sometimes people prefer to use the A1 notation instead of defining cells by rows and columns. There’s nothing wrong with that if that’s what they want to do!

Parting Tips

Before I go, I want to remind you that you can use this function in an Excel formula if you need to! I’m not sure why you would, but you can! For example, entering the formula

=ColumnLetter(A2)

into a cell will return the column letter associated with whatever number is entered into cell A2.


Reach out to my VBA Consulting page if you’re working on a macro, but you can’t quite figure out how to finish it. I’ll be happy to help you out!

Please subscribe to my email list and share this article with your friends on Facebook, Twitter, and Google+.

If you’re ready to free up your time in the office, visit my Excel Add-ins page for some great automation products. There are plenty more grab-and-go macro examples in my VBA Code Library so take a look there, too!

Check out more VBA Tutorials

About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA products, including Mouse To Macro. See more Excel products, including online courses and books, by visiting the Excel VBA Store.

Excel VBA Store