Macro concatenate formula

[vipjun]

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

 

[Bob Phillips]

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]

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