VBA to Concatenate

Hello All,

I'm looking to use VBA to concatenate variable data in columns F:J into column F and could use some help to point me in the right direction. The data in this range will be variable, meaning the number of rows will be unknown, and there will also be unused cells, as illustrated in the attached example workbook.
This will be part of a larger macro in a master file that copies the concatenated data from column F and pastes it into a sheet in the master file where the macro is stored.
Here is the code I am using so far:

Please Login or Register  to view this content.

Any help would be greatly appreciated, thanks!

Maybe:

Please Login or Register  to view this content.

Originally Posted by OllyXLS

No quite clear on what you want - do you just want the code to concatenate values in columns F:J for all rows with data? Do you need any form of delimiter between the concatenated values?

Do you then want columns G:J to be cleared, or to stay the same?

Hello OllyXLS, thanks for the reply!

Yes, I'm just looking for the code to concatenate those values.
I forgot to include that the delimiter between values should be a space.
Columns G:J can stay as they are, I will only be pulling the data from column F.

Thanks!

Originally Posted by JOHN H. DAVIS

Maybe:

Please Login or Register  to view this content.

Hello John, thanks for the reply!
Testing your code now, how do I get spaces in between the vaules? Thanks!

Originally Posted by OllyXLS

Try:

Please Login or Register  to view this content.

Thanks OllyXLS, L'll be testing this shortly!

This will add spaces

Please Login or Register  to view this content.

Originally Posted by JOHN H. DAVIS

This will add spaces

Thanks John, this worked great!
What does this do:

Please Login or Register  to view this content.

Never saw that before!

Originally Posted by OllyXLS

Careful, though - that will also add spaces even when no value exists in the respective G:J cell... So you may end up with a concatenated cell in F, which has several spaces at the end. Depending on what you're doing next with your data, this may or may not present a problem!

Thanks Olly, in this instance this does not present a problem.

Here is the final version that I went with:

Please Login or Register  to view this content.

Thanks for all the help, much appreciated! Marking this as solved.

Originally Posted by Pierce Quality

Thanks John, this worked great!
What does this do:

Please Login or Register  to view this content.

Never saw that before!

The 2 is a row count to stop at - in this case Row 2.
Step -1 - means from the last row in the column it moves up -1 row.

Originally Posted by JOHN H. DAVIS

The 2 is a row count to stop at - in this case Row 2.
Step -1 - means from the last row in the column it moves up -1 row.

Makes sense, thanks John!

Welcome. Glad to help out.

Originally Posted by JOHN H. DAVIS

Welcome. Glad to help out.

John,
ive been having an issue with an unanticipated variable, sometimes the cells in Column F will be empty, but there will still be data in Columns G:J to merge into column F. Currently, if there is no data in column F nothing is merged.
Any help would be greatly appreciated, thanks!

Can you attach a file where that is occuring. I ran a test on some dummy data and it appeared to work.

Originally Posted by JOHN H. DAVIS

Can you attach a file where that is occuring. I ran a test on some dummy data and it appeared to work.

Thanks John, will do!

John,

In the attached examples the data labeled "Data2" is not appearing in the master file.

Thanks!

Originally Posted by Pierce Quality

John,

In the attached examples the data labeled "Data2" is not appearing in the master file.

Thanks!

I don't see a Data2? Which Columns are you trying to concatenate based on your examples? Is the error occuring in Book1, Book2 or both? Please explain.

Try:

Please Login or Register  to view this content.

I think I understand.

Sorry John, I missed the letter in my previous post, its the data Labeled "DataG2" in Book2 thats not appearing to concatanate in the corresponding in column F when when the corresponding cell column F is empty.

Thanks

Originally Posted by Pierce Quality

Sorry John, I missed the letter in my previous post, its the data Labeled "DataG2" in Book2 thats not appearing to concatanate in the corresponding in column F when when the corresponding cell column F is empty.

Thanks

Did Post 18 fix the problem?

Originally Posted by JOHN H. DAVIS

Did Post 18 fix the problem?

Just ran your updated version and it solved the problem!
Thanks for the help again John, much appreciated!

You're welcome. The Code was looking for the last used cell in Column F. I changed it to the last used row on the Sheet. I assume Column F to be equal to the others.

Thanks John, you are correct!