General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

A more flexible ColumnsToText

As with the hard-coded version, this macro will merge the values in each row of the Selection, if no range is provided. However, it will also accept a range to merge , and a delimiter, as arguments.

If called from VBA, a delimiter string can be inserted between values by specifying it in the second argument.

Note, this Sub uses optional arguments to provide flexibility. That, however, prevents the macro from being listed in the Tools/Macro/Macros macro list. You can still type the name of the macro in and click Run, or, you can create a wrapper that you can attach to a button, menu item or keyboard shortcut (see below).

    'Purpose:   Merge values in a multi-column array into the first column
    'Inputs:     rRng (optional): the range to merge. If not provided, the Selection.
    '               sDelim (optional): A delimiter string to go between values.
    'Returns:   Merged values in column 1 of rRng
        Public Sub ColumnsToText(Optional rRng As Range, _
                Optional sDelim As String = "")
        'J.E. McGimpsey http://www.mcgimpsey.com/excel/mergedata1.html
        Dim vTxtArr As Variant
        Dim nTop As Long
        Dim i As Long
        Dim j As Integer
        If rRng Is Nothing Then Set rRng = Selection
        Set rRng = Intersect(rRng, rRng.Parent.UsedRange)
        vTxtArr = rRng.Value
        nTop = UBound(vTxtArr, 1)
        For i = 1 To nTop
            For j = 2 To UBound(vTxtArr, 2)
               vTxtArr(i, 1) = vTxtArr(i, 1) & sDelim & vTxtArr(i, j)
            Next j
        Next i
        ReDim Preserve vTxtArr(1 To nTop, 1 To 1)
        rRng.Resize(, 1).Value = vTxtArr
    End Sub

An example of a wrapper macro might be

    Public Sub CTT_Button_Click()
        ColumnsToText Selection, " "
    End Sub

which you could place in your Personal Macro Workbook (Personal.xls for WinXL) and attach to a toolbar button to merge with spaces in between cell entries.

Merging a range into one cell

Again, like the hard-coded macro, this macro merges the entire Selection or other range you specify into one cell (and again, since there are optional arguments, it won't show up in your Tools/Macro/Macros dialog, but you can still type the name MergeToOneCell in the textbox and click Run, or you can attach the macro to a toolbar button, menu item, or keyboard shortcut). If run from code, it can insert a delimiter between cells:

    'Purpose:   Merge cells, retaining all data
    'Inputs:    rRng (optional): the range to merge. If not provided, the Selection.
                sDelim (optional): A delimiter string to go between values.
    'Returns:   Merged values in the first cell of rRng
    Public Sub MergeToOneCell(Optional rRng As Range, _
            Optional sDelim As String = "")
        'J.E. McGimpsey, http://www.mcgimpsey.com/excel/mergedata1.html
        Dim rCell As Range
        Dim sMergeStr As String
        If rRng Is Nothing Then Set rRng = Selection
        With rRng
            For Each rCell In .Cells
                sMergeStr = sMergeStr & sDelim & rCell.Text
            Next rCell
            Application.DisplayAlerts = False
            .Merge Across:=False
            Application.DisplayAlerts = True
            .Item(1).Value = Mid(sMergeStr, 1 + Len(sDelim))
        End With
    End Sub

As above, you could use a wrapper macro to merge the selected cells with a space or other delimiter.

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Where to put your macros

Unfamiliar with macros? Check out David McRitchie's Getting Started with Macros and User Defined Functions