| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
merging cells and formating text.
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
merging cells and formating text.
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 -------------------------------------------- Then, assuming it isn't a very long string, just open a file and write it? "Daniel M" wrote in message nk.net... 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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
merging cells and formating text.
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" wrote in message ... 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 -------------------------------------------- Then, assuming it isn't a very long string, just open a file and write it? "Daniel M" wrote in message nk.net... 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
merging cells and formating text.
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] "Daniel M" wrote in message k.net... 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" wrote in message ... 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 -------------------------------------------- Then, assuming it isn't a very long string, just open a file and write it? "Daniel M" wrote in message nk.net... 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
merging cells and formating text.
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? "Tim Coddington" wrote in message ... 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] "Daniel M" wrote in message k.net... 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" wrote in message ... 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 -------------------------------------------- Then, assuming it isn't a very long string, just open a file and write it? "Daniel M" wrote in message nk.net... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| merging text from two cells | Excel Discussion (Misc queries) | |||
| Text from 2 Cells merging to another...Help | Excel Discussion (Misc queries) | |||
| Merging cells and text wrap | New Users to Excel | |||
| Merging cells with text as one line | Excel Worksheet Functions | |||
| Merging cells text | Excel Programming | |||
![Daniel M[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
