Combine multiple cells into one in excel with macro?

Question

I have a similar question to this one:

http://stackoverflow.com/questions/621862/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

Hi Mike,

That custom function was just what I was looking for. Thanks very much!