Concatenate multiple cells by vba

[sharad.sony]

I have an excel sheet and cell B2 I declare as integer.

I wish that if the cell b2 value is 3, cell b3 to d3 concatenate with ";" like concatenate(b3;c3;d3) and put this concatenated value to cell b4.

if the cell b2 value is 5, cell b3 to f3 concatenate with ";" like concatenate(b3;c3;d3;e3;f3) and put this concatenated value to cell b4.

Please help.

[Leith Ross]

Hello sharad.sony,

This macro will do it.



Sub MultipleCellConcatenate()


Dim Data() As Variant
Dim n As Integer

On Error Resume Next
n = Range("B2").Value
If Err <> 0 Then
MsgBox "The value must be an Integer.", vbExclamation
Exit Sub
End If
On Error GoTo 0

ReDim Data(1 To n)

Data = Range("B2").Offset(1, 0).Resize(1, n).Value

Data = Application.Transpose(Data)
Data = Application.Transpose(Data)

On Error Resume Next
Range("B4").Value = Join(Data, ";")
If Err <> 0 Then Range("B4").Value = ""
On Error GoTo 0

End Sub

[sharad.sony]

Great, it works . Thanx a lot Mr. Leith Ross