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