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