Macro concatenate formula

Hi everyone,

Im sure we all know that concatenate can be a really pain in the butt function when you have alot of cells to concatenate and also theres a 30 cell limit.

so how would one concatenate
Column A
texta
textb
textc
textd

to

Column A
texta|textb|textc|textd

There would probably be a loop function until it finds a blank cell on that column then it would stop. Also some columns might have more than 30cells. I have not created any macro's on excel so this would be my first example to learn from

Thank you all,
vipjun

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
Cells(i - 1, "A").Value = Cells(i - 1, "A").Value & "|" & _
Cells(i, "A").Value
Cells(i, "A").Clear
Next i

End Sub



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"vipjun" <[email protected]> wrote in
message news:[email protected]...
>
> Hi everyone,
>
> Im sure we all know that concatenate can be a really pain in the butt
> function when you have alot of cells to concatenate and also theres a
> 30 cell limit.
>
> so how would one concatenate
> Column A
> texta
> textb
> textc
> textd
>
> to
>
> Column A
> texta|textb|textc|textd
>
> There would probably be a loop function until it finds a blank cell on
> that column then it would stop. Also some columns might have more than
> 30cells. I have not created any macro's on excel so this would be my
> first example to learn from
>
> Thank you all,
> vipjun
>
>
> --
> vipjun
> ------------------------------------------------------------------------
> vipjun's Profile:
http://www.excelforum.com/member.php...o&userid=35266
> View this thread: http://www.excelforum.com/showthread...hreadid=552430
>

Thanks bob I was able to edit the macro for different columns.