Code to concatenate multiple cells in a range using excel macro

Code to concatenate multiple cells in a range using excel macro

 Sub Combine_Multiple_Cells()  
 On Error Resume Next  
 Application.DisplayAlerts = False  
 Dim Myrange As Range  
 Dim mydelim As String  
 Dim Combine As String  
 Dim eachcell As Range  
 Dim getvalue As Range  
 Set Myrange = Application.InputBox(Prompt:="Please select a range with your Mouse to be Concatenated.", Title:="dseri", Type:=8)  
 On Error GoTo 0  
 If Myrange Is Nothing Then  
 Exit Sub  
 Else  
 mydelim = Application.InputBox(Prompt:="Please enter a Delimiter.", Title:="Tejas Gandhi")  
 For Each eachcell In Myrange  
 Combine = Combine & mydelim & eachcell.Text  
 Next eachcell  
 Set getvalue = Application.InputBox(Prompt:="Select a Cell where you want the Combination.", Title:="dseri", Type:=8)  
 getvalue = Right(Combine, Len(Combine) - 1)  
 End If  
 End Sub  

One more option is there to create a User Defined Function (UDF)

 Function Combine(MyRange As Range, Optional Delimiter As String = " ")  
 Dim myAdd As String  
 myAdd = MyRange.Address  
 Combine = Join(Evaluate("transpose(" & myAdd & ")"), Delimiter)  
 End Function