Merge multiple cells into one in excel with macro?

Question

I have a similar question:

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

But I want to combine a range of cells inside a column, like A2: A50. Sometimes I have more than 300 cells that can be combined into one. Values ​​are text. Is there a way to change this macro to work with a range instead of two cells?

Thanks!

Answer 1

Based on the stream you are quoting , I think you want to return the concatenation of all the values ​​stored in the cells, interpreting all the values ​​as strings?

For this, you can use the VBA macro, which 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 can name it as follows:

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

Is this what you were looking for?

Mike

Answer 2

Try using the following macro, which is not very elegant, since it does not perform error checking, etc., but works. Assign the macro to the button, click the cell, click the macro button, select the desired (source) range to combine with the mouse (it will be automatically filled in the input field in the dialog box), click "OK", highlight the destination cell (it will autofill the input window in the next dialog box) click "OK", all cells will be merged with one space into the destination cell, which may be in the original source range). It's up to you to remove the extra cells manually. Work with two rows and columns, but not with 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 

Answer 3

Just add to Mike's solution if you want to get your range from a variable instead of a specific range (I had problems with the syntax):

 Sub MyMacro() dim myVar As Range MsgBox ConcatinateAllCellValuesInRange(myVar) End Sub