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