This post will guide you how to combine multiple cells into one cell with VBA code in Excel. How do I concatenate a range of cells with VBA macro in Excel 2013/2016. How to merge the contents of two or multiple cells into one cell with macro in Excel.

Merge multiple Cells into One Cell with VBA

---

If you want to merge multiple cells into one cell without losing data in excel, you can try to use an Excel VBA macro to quickly achieve the result. Just do the following steps:

#1 open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub MergeCells()
    Dim xJoinRange As Range
    Dim xDestination As Range

    Set xJoinRange = Application.InputBox(prompt:="Highlight source cells to merge", Type:=8)
    Set xDestination = Application.InputBox(prompt:="Highlight destination cell", Type:=8)
    temp = ""
    For Each Rng In xJoinRange
        temp = temp & Rng.Value & " "
    Next
    xDestination.Value = temp
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 select one range of cells to merge cells

#7 select one destination cell

#8 let’s see the result.

You can also write an user defined function with VBA to merge multiple cells in Excel. just do use the following vba code:

Function MergeCells(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

    MergeCells = finalValue

End Function