Sub ConCat()
Dim v(1 To 9)
Dim i As Long, ii As Long, j As Long
Dim lim As Long, s As String, jj As Long
Dim rng As Range, rng1 As Range
Set rng = Range(Cells(1, 2), Cells(1, "IV").End(xlToLeft))
i = 1
ii = 0
j = 2
lim = Application.Min(rng.Count + 1, 175)
Do While j <= lim
v(i) = v(i) & Cells(1, j).Address(0, 0) & ","
ii = ii + 1
j = j + 1
If j lim Then Exit Do
If ii = 29 Then
ii = 0
i = i + 1
End If
Loop
s = "="
For jj = 1 To i
v(jj) = Left(v(jj), Len(v(jj)) - 1)
s = s & "Concatenate(" & v(jj) & ")&"
Next
s = Left(s, Len(s) - 1)
Set rng1 = Range(Range("B1"), Range("B1").End(xlDown))
rng1.Offset(0, -1).Formula = s

End Sub

--
Regards,
Tom Ogilvy


"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.