merging cells and formating text.

[Daniel M]

I have data in 2 columns that i need to merge.

IE:
A B
234 245

needs to be:
234245

Now i know i can concantenate the data but then i'm stuck.

The data can be of any number of rows, so i need to select only the cells
with data in them. I then have to take the data and transpose it.

IE:
A
234
456
689

needs to be:
A B C
234 456 689.

This way i can save it as comma delimited and import it into another source.
Basically i need my ending data to be 123,345,677,8984.

Any ideas on how to get this all done? I would like to make it a macro so i
dont have to manually do it each time. thanks.

 

[Tim Coddington]

If the cursor is in the column in question, how zabout ...
--------------------------------------------
Private Sub CLDColumn()
Dim CDLString$

Cells(65536, ActiveCell.Column).End(xlUp).Select
Do Until ActiveCell.Row = 1
CDLString$ = "," & ActiveCell.Value & CDLString$
ActiveCell.Offset(-1, 0).Select
Loop
CDLString$ = ActiveCell.Value & CDLString$
End Sub

 

[Daniel M]

This doesnt seem to work right, it goes to the bottom of the lister and
starts moving up but then stops when it is done. doing nothing. Once again
here is my data...

2 columns
a b
212 2132
213 21321
465 1669
7948 8989

This goes on for 40+ lines.

i then need the data to be in one cell merged together like so...

2122132
21321321
4651669
79488989
....

once done i need the data to be transposed like so...

a b c d...
2122132 21321321 4651669 79488989...

 

[Tim Coddington]

Ooooh. Must have misunderstood the problem. But you were able to view the
CDLString$, weren't you in the debugger?

So try again. Perhaps you could put a formula in column c to help? Like
=A1&B1 in each row? Then go something like ...
Sub trytranspose()
Range("C1:C4").Copy 'Only for the C4 use the end(xlup) trick
[D1].PasteSpecial xlPasteValues, , , True 'Here, there must be fewer
than 256 - 3 columns used = 252 rows
End Sub
My sample comes out like ...
212
2132
2122132
2122132
21321321
4651669
79488989

213
21321
21321321





465
1669
4651669





7948
8989
79488989






May need to delete those first three columns so the array starts at [A1]

 

[Tim Coddington]

OK. So when I copied the sample into the last e-mail message, it kind of
messed it up. Please ignore.
But by what method were you taking the data and writing it to the .CDL file?

Ooooh. Must have misunderstood the problem. But you were able to view the
CDLString$, weren't you in the debugger?

So try again. Perhaps you could put a formula in column c to help? Like
=A1&B1 in each row? Then go something like ...
Sub trytranspose()
Range("C1:C4").Copy 'Only for the C4 use the end(xlup) trick
[D1].PasteSpecial xlPasteValues, , , True 'Here, there must be fewer
than 256 - 3 columns used = 252 rows
End Sub
My sample comes out like ...
212
2132
2122132
2122132
21321321
4651669
79488989

213
21321
21321321





465
1669
4651669





7948
8989
79488989






May need to delete those first three columns so the array starts at [A1]

This doesnt seem to work right, it goes to the bottom of the lister and
starts moving up but then stops when it is done. doing nothing. Once again
here is my data...

2 columns
a b
212 2132
213 21321
465 1669
7948 8989

This goes on for 40+ lines.

i then need the data to be in one cell merged together like so...

2122132
21321321
4651669
79488989
...

once done i need the data to be transposed like so...

a b c d...
2122132 21321321 4651669 79488989...

macro

so