Combine multiple cells into one in excel with macro?

Question

I have a similar question to this one:

Merge the contents of 2 cells into another 3rd cell using VBA in Excel

But I want to combine a range of cells within a column, eg A2:A50. Sometimes I have over 300 cells to be combined into one. Values are text. Is there any way to modify this macro so that it works on a range instead of just two cells?

Thanks!

Answer 1

Based on the thread you are citing, I guess you wish to return the concatination of all the values held by the cells, interpreting all the values as strings?

For that, you could use a VBA macro that looks like this:

Function ConcatinateAllCellValuesInRange(sourceRange As Excel.Range) As String
    Dim finalValue As String

    Dim cell As Excel.Range

    For Each cell In sourceRange.Cells
        finalValue = finalValue + CStr(cell.Value)
    Next cell

    ConcatinateAllCellValuesInRange = finalValue
End Function

As an example, you could call it like this:

Sub MyMacro()
    MsgBox ConcatinateAllCellValuesInRange([A1:C3])
End Sub

Is this what you were looking for?

Mike

Answer 2

Try the following macro, not very elegant in that it doesn't do any error checking etc but works. Assign the macro to a button, click in a cell, click the macro button, highlight the desired (source) range to merge using your mouse (will autofill in range in the input box in the dialogue box), click ok, highlight the destination cell (will autofill the input box in the next dialogue box) click ok, all cells will be merged with a single space character into the destination cell, which can be in the original source range). Up to you to delete the superfluous cells manually. Workks with both rows and columns but not blocks.

Sub JoinCells()

Set xJoinRange = Application.InputBox(prompt:="Highlight source cells to merge",    Type:=8)
xSource = 0
xSource = xJoinRange.Rows.Count
xType = "rows"
If xSource = 1 Then
    xSource = xJoinRange.Columns.Count
    xType = "columns"
End If
Set xDestination = Application.InputBox(prompt:="Highlight destination cell", Type:=8)
If xType = "rows" Then
    temp = xJoinRange.Rows(1).Value
    For i = 2 To xSource
        temp = temp & " " & xJoinRange.Rows(i).Value
    Next i
Else
    temp = xJoinRange.Columns(1).Value
    For i = 2 To xSource
        temp = temp & " " & xJoinRange.Columns(i).Value
    Next i
End If

xDestination.Value = temp

End Sub