# An Array of Column Letters

From Dennis:

Any idea how I can convert a range of columns expressed as a single
multi-part range, e.g. “C:D,F:I,L:Q”, into an array of strings, i.e. a[0] =
“C”, a[1] = “D”, a[2] = “F”, a[3] = “G” etc?

Here’s what I came up with:

Sub TestColToArr()

Dim rRng As Range
Dim vaCols As Variant
Dim i As Long

Set rRng = Sheet1.Range(“C:D,F:I,L:Q”)

vaCols = ColumnsToArray(rRng)

For i = LBound(vaCols) To UBound(vaCols)
Debug.Print vaCols(i)
Next i

End Sub
Function ColumnsToArray(rInput As Range) As Variant

Dim rCol As Range
Dim rAreas As Range
Dim aColLets() As String
Dim i As Long

For Each rCol In rInput.Columns
i = i + 1
ReDim Preserve aColLets(1 To i)
aColLets(i) = ColumnLetter(rCol.Column)
Next rCol

ColumnsToArray = aColLets

End Function

Function ColumnLetter(ByVal colNum As Long) As String
‘Written by Gary Waters
Dim i As Long, x As Long
For i = Int(Log(CDbl(25 * (CDbl(colNum) + 1))) / Log(26)) – 1 To 0 Step -1
x = (26 ^ (i + 1) – 1) / 25 – 1
If colNum > x Then
ColumnLetter = ColumnLetter & _
Chr(((colNum – x – 1) &#92; 26 ^ i) Mod 26 + 65)
End If
Next i
End Function
Posted in Uncategorized

## 7 thoughts on “An Array of Column Letters”

1. Hi Dick,

Typo:
In the function ColumnLetter with the line starting with Chr there is a missing backslash – just before the number 26 should go a backslash (integer divide)

Out of interest, the ColumnLetter function I use is

Cheers,
Rob

2. Dick,

Just another way of doing it. Seems to execute in about the same time.

Private Const msRANGE As String = “C:D,F:I,L:Q”

Sub Demo()
Dim rngCol As Range
Dim sTemp As String
Dim lIdx As Long

For Each rngCol In Range(msRANGE).Columns
InStr(1, sTemp, “:”, _
vbBinaryCompare) – 1)
lIdx = lIdx + 1
Next rngCol
End Sub

3. Gary Waters says:

Dick,

Thanks for including my code. I did discover later on that you can eliminate the ‘if colNum > x Then’ statement because of the ‘i’ variables initialization.

4. Dick says:

Not a typo, just WordPress assuming it knows how to code HMTL. The syntax hiliter is the best code rendering I’ve seen, but it still sucks. Any code with an ampersand or integer division is at risk. I’ll see if I can figure out how to escape it.

5. Dennis says:

That’s fantastic. Thanks everyone. My version had me monkeying around with Areasquite unnecessarily as it turns out!

(Thanks also to RobI saw your ColumnLetter function on your website a while ago, and started using it because it’s about 15 lines shorter than my own stab at this!)

6. Like Dennis, I’ve always cycled through the areas when I have a discontiguous range. I had to test this to be sure it worked.

Rob’s column number to letter code is pretty cool, too.

7. keepITcool says:

More ColumnLetter functions..
shorter and 2x faster than Gary’s overcomplex code. >10x faster than Rob’s elegant one-line solution

Function ColumnLetter(ByVal colNum As Long) As String
colNum = (colNum – 1) Mod 256
If colNum > 25 Then ColumnLetter = Chr\$(64 + colNum 26)
ColumnLetter = ColumnLetter & Chr\$(65 + colNum Mod 26)
End Function

It does have a drawback if Excel ever gets beyond the 256 column boundary.. Following s/b future proof.

Function ColumnLetter(ByVal colNum As Long) As String
Do
ColumnLetter = Chr\$(65 + (colNum – 1) Mod 26) & ColumnLetter
colNum = (colNum – 1) 26
Loop While colNum > 0
End Function

Posting code? Use <pre> tags for VBA and <code> tags for inline.