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
Make powerful macros with our free VBA Developer Kit This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below.
This is actually pretty neat. If you have trouble understanding or remembering it, our free VBA Developer Kit can help. It’s loaded with VBA shortcuts to help you make your own macros like this one - we’ll send a copy to your email address below.
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!
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
into a cell will return the column letter associated with whatever number is entered into cell A2.
That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.
The best free VBA training on the web
I see people struggling with Excel every day and I want to help. That's why I'm giving away my 90-days to Master VBA eCourse and my entire personal macro library for free.