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) \ 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,

    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)


  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 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

  3. 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. 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. 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. 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
    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.

Leave a Reply

Your email address will not be published. Required fields are marked *