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


--
vipjun
------------------------------------------------------------------------
vipjun's Profile: http://www.excelforum.com/member.php...o&userid=35266
View this thread: http://www.excelforum.com/showthread...hreadid=552430

Macro concatenate formula
 

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" wrote in
message ...

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

Macro concatenate formula
 

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


--
vipjun
------------------------------------------------------------------------
vipjun's Profile: http://www.excelforum.com/member.php...o&userid=35266
View this thread: http://www.excelforum.com/showthread...hreadid=552430