Create a UDF

Function JoinCells(rng As Range, Optional Delimiter As String = ",")
Dim tmp As String

If rng.Columns.Count 1 Then
If rng.Rows.Count 1 Then
JoinCells = CVErr(xlErrRef)
Exit Function
End If
tmp = Join(Application.Transpose(Application.Transpose(r ng)),
Delimiter)
Else
tmp = Join(Application.Transpose(rng), Delimiter)
End If
If Len(Delimiter) 0 Then
Do Until Right(tmp, 1) < Delimiter
tmp = Left(tmp, Len(tmp) - 1)
Loop
End If
JoinCells = tmp
End Function


and use like

=JoinCells(B1:M1,"")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Vadhimoo" wrote in message
...
Hi,
I am having a more data in every column(around 100 columns..).
I want to do concatenate all the cell values into the first column
(A1=CONCATENATE(B1,C1,D1,..DC)
A2=CONCATENATE(B2,C2,D2,....EA)

Any macro? because total no of columns value may be changed.

Thanks in advance for your reply.