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) \ 26 ^ i) Mod 26 + 65)

End If

Next i

End Function

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) \ 26 ^ i) Mod 26 + 65)

End If

Next i

End Function

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

Split(Columns(colNum).Address(, False), “:”)(1)

Cheers,

Rob

Dick,

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

Sub Demo()

Dim rngCol As Range

Dim sTemp As String

Dim asNewAddr() As String

Dim lIdx As Long

For Each rngCol In Range(msRANGE).Columns

ReDim Preserve asNewAddr(lIdx)

sTemp = rngCol.Address(False, False)

asNewAddr(lIdx) = Left$(sTemp, _

InStr(1, sTemp, “:”, _

vbBinaryCompare) – 1)

lIdx = lIdx + 1

Next rngCol

End Sub

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.

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.

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

(Thanks also to RobI 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!)

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.

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