VBA: Concatenate Strings in Adjacent Cells with a Space between them?

Hello

I've been trying to write a macro to solve this problem all morning, so thought it was time to ask for help...

I'd like to concatenate the text in 2 rows.

Take the text in cells A1 to A100, add the text in adjacent cells B1 to B100 (adding a space between them) and output the result in the C column.

The data in the B column sometimes ends in a space, so the output in the C column also needs to be trimmed to remove any trailing spaces.

Any suggestions much appreciated, and I promise to mark this thread as solved when I find a solution


Robin

No idea why this would be a macro, but ...

Please Login or Register  to view this content.

All the 'TRIMs' to get rid of any leading/trailing spaces plus also the intermediate space if Col A or Col B is blank

(Typed freehand - testing was precisely zero)

Thank you for your suggestion

I am already using VBA to import data and therefore wanted a VBA solution rather than using an XLS formula.

This code only works if the cells are adjacent, what would be a solution if the columns I'm adding are far apart please? (So I can specify the columns absolutely rather than using an offset)

Also is there a solution without using a loop, since I have a lot of data and this will be quite slow.

Thanks

At once

Please Login or Register  to view this content.

Thank for this code, which I have tried and is very fast, but it puts the same result from the first row into every row?

HTML Code: 

Thanks to JEC this code quickly does what I want, but it puts the result from the first row into every subsequent row. How can we get this to move down one on each row please?

For me it's working as expected and how you say it should work.

Originally Posted by vonsworld

Take the text in cells A1 to A100, add the text in adjacent cells B1 to B100 (adding a space between them) and output the result in the C column.

The data in the B column sometimes ends in a space, so the output in the C column also needs to be trimmed to remove any trailing spaces.

try

Please Login or Register  to view this content.

Maybe it's because I'm using Excel 2010? I'm attaching an XLS with your VBA and the duplicate results.

Jindon, That works perfectly, Thank You!

For a dynamic range:

Please Login or Register  to view this content.