Merging multiple cells with the additional text

[enhydra]

Hello Everyone,

I need some VBA code to merge cells A thru G plus the addition of a text word. The cells need to be placed in cells in column J. The cells A thru G need to be reversed and a "-" placed between then and the additional text word. The last two cells do not need the "-" between them. The rows will vary in length anywhere from 100 rows to 50000. Maybe if someone has some sample code to get me started, I’m pretty confused at where to start. I’ve attached a sample book of what the final outcome should look like.

Thanks,

Enhydra

[Leith Ross]

Hello enhydra,

You don't need VBA for this. Copy this formula into "J1" and drag it down to the column.

=CONCATENATE("Port ",E1,"-",D1,"-",C1,"-",B1,"-",A1," ",F1, " ",G1)

[enhydra]

The reason I said I need a VBA, is because the sheet will not exist in the begining. It will need to be created and filled with data before perfoming the merge. Is this still possible.

E.

[Leith Ross]

Hello enhydra,

Before I can help you with the VBA code, I need some more information.

1) Will this new sheet be created manually or by code?
2) How will the macro be run manually or automatically?
3) If automatically, will it be when the new sheet is created?
4) Are the columns in the example workbook the same as in the actual workbook?

[enhydra]

1.) Sheet being created by code
2.) macro run automatically
3.) after sheet is created and after the sheet gets data imported to it
4.) Yes, the columns are the same.

Thanks

E.

[Leith Ross]

Hello enhydra,

I didn't forget about you. I just finished a couple of other projects. I have one more question. How will I know when the data has been loaded on the new sheet?

[enhydra]

I was just going to call a subroutine after the data is loaded. I've been trying the attached code and it sort of works but it doesn't loop to the next row. I'm somewhat over my head.

Please Login or Register  to view this content.

[Leith Ross]

Hello enhydra,

After looking over the macro, wouldn't all the inputs be on the same row as in the example workbook?

[enhydra]

Yes, that's part of the problem. I was just puting this together and trying to fix the problems. Like I said, I'm in over my head. I'm trying to get it to look just like the sample workbook.

[enhydra]

Ok, I got this to work with one row, now how do I get this to loop through all the rows until the colums are empty?

Thanks
E.

Please Login or Register  to view this content.

[Leith Ross]

Hello enhydra,

Sorry for the delay. Things have been hectic. This macro will examine each sheet starting with cell "A1" and add the the built string into the cell in column "J" of each row.

Please Login or Register  to view this content.

[enhydra]

Thank you Leith.

It's not doing exactly what I want.

It seems to be only merging 1-2-3-4-5. If I change any of the data, it still only merges 1-2-3-4-5 to 5-4-3-2-1.
The data will be change every time it gets loading into the sheet.

It also seems to overwrite the first merge in cell J1 to Port-----.at the end if the macro run.

Thanks
E.

[Leith Ross]

Hello enhydra,

Okay, that scores pretty high on the weird meter. I made a few changes and tested this on the sample workbook and it ran. Check it on the real workbook and let me know if any strange happens.

Please Login or Register  to view this content.

[enhydra]

Yes, thank you Leith, that works great.

I do have one more question. If I want to eliminate one or more of the columns in the range can this be done with the same code? If this requires completely different code then never mind, I was just curious because this looks like it would come in handy for some of my other problems.

Thanks again.

E.

[Leith Ross]

Hello enhydra,

You could eliminate columns by making some minor changes to the macro. What did you have in mind?

[enhydra]

Ok, lets say I want to eliminate column C and E.

[Leith Ross]

Hello enhydra,

Since the columns are consecutive, you can change the loop index to this...

Please Login or Register  to view this content.

This will now use only columns "A" and "B". Column "F:G" and "J" will remain unchanged.

[enhydra]

That’s cool. Thank you very much Leith. I really do appreciate all the help. This will save me hours of time trying to figure this out (actually it will save me days).
I will mark this as solved.

Thanks,
E.

[Leith Ross]

Hello enhydra,

You're welcome. If you have any questions later on, just ask. Thanks for marking the post solved.