Solved: Change column into one line of text

[Gert Jan]

Hi,
Here's a procedure i follow a couple of times a week.
I have a column with (not a fixed number of)text values, something like this:

aaa
bbb
ccc
etcetera..

I copy paste this into notepad>>remove the cr/lf>>put a comma after each value, so that i have one line of text. This line i copy/paste into a searchbox of another program. The last step can only be done manually, but the first part could be a lot more simple i guess.
Any suggestions on this one??

Gert Jan

[mdmackillop]

Select the first cell and run the following macro

Sub Joins()
Dim cel as range, FCell As Range, Str As String
Set FCell = ActiveCell
For Each cel In Range(FCell, FCell.End(xlDown))
Str = Str & cel & ", "
Next
Str = Left(Str, Len(Str) - 2)
FCell.Offset(, 1) = Str
End Sub

[Gert Jan]

Hi Malcolm,
thanks for reacting, i think this does the trick. I have to wait until tomorrow before i can do a "live" test but right now it works.
Two questions though, in the line
Str = Str & cel & ", "
there is a space, when i remove that, the loop runs until an error "out of memory" occurs. I don't know yet if the other application accepts this space.
If not, is there a way to get this space out?
Would it be possible to, when this code has run, select the output cell and set it to copy (ctrl+c) so that i only have to hit ctrl+V in the other application?

Gert Jan

edit: please disrecard question about copy, i i've got that one.

[mdmackillop]

No problem.

Sub Joins()
Dim cel As Range, FCell As Range, Str As String
Set FCell = ActiveCell
For Each cel In Range(FCell, FCell.End(xlDown))
Str = Str & cel & ","
Next
Str = Left(Str, Len(Str) - 1)
FCell.Offset(, 1) = Str
FCell.Offset(, 1).Copy
End Sub

[Gert Jan]

edit: please disrecard question about copy, i i've got that one.
LOL, just to late,

Thanks a lot for your help.

Gert Jan

[Gert Jan]

I did a testrun today, and your second code (without the space) works perfect.

Thanks again,

Gert Jan