Merge Multiple Cells into One Cell with VBA


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.

Get the position of the nth using excel vba1

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

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

convert column number to letter3

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

merge multiple cells1

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 & " "
    xDestination.Value = temp
End Sub

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

merge multiple cells2

#6 select one range of cells to merge cells

merge multiple cells3

#7 select one destination cell

merge multiple cells4

#8 let’s see the result.

merge multiple cells5

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

merge multiple cells6

Leave a Reply