Combining Cells into One Cell

[KrNpRiDe]

Hi, all.
I am trying to combine multiple cells into 1 cell per row.
I would have "A" column empty, then combine "B", "C", and on into the "A" column.
Each cell value will be separated by a space or any special character I designate in the macro

I have attached an example which the output is separated by a space.
Sheet1 has the original file and Sheet2 has the desired output.
Thank you for all your help in advance.

[stanleydgromjr]

KrNpRiDe,

Try:

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel

Please Login or Register  to view this content.

Then run the "CombineText" macro.

[Leith Ross]

Here is another version that will size the range automatically. It is set to put the combined data into column "A". You are prompted for the separator when the macro runs. This can be hard coded if you like. A button has been added to the worksheet to run the macr.

Please Login or Register  to view this content.

[KrNpRiDe]

Thank you all for fast response.

stanleydgromjr, your code only works for 3 column field, but works great for for that purpose.

Leith Ross, your code works great, but gives an error message:

"Run-time error '1004': Application-defined or object-defined error"
The line in the code is "Rng.value = Data"

I believe the error occurs due to the range of column is not changing.

How can I make the number of columns change for every row?

Thank you.

[Leith Ross]

Hello KrNpRiDe,

The macro I wrote is attached to the workbook (Excel 2003 format) in my previous. Since there have been no downloads of the file, I would suggest you do so. The problem maybe version related. Your profile says you are using Excel 2007. Download the workbook that has the macro and see how it performs on your system.

[KrNpRiDe]

Thanks Leith Ross.

I have tried your code with the file that you provided.
The number of column stays constant through out the program.
I need it to be variant per each row.
I have changed the sample file and attached it below.
Please let me know how I can change the number of columns for each row.

Thank you in advance.

[KrNpRiDe]

Sorry, forgot add the attachment.

[KrNpRiDe]

Sorry, new to this forum. File attached.

[shg]

Try this:

Please Login or Register  to view this content.

[KrNpRiDe]

Thank you shg.

Your code worked perfectly.

Thank you all for great help and fast responses.