Merge columns based on condition

I am new to Macro coding so this should be a simple request but the code of these can be intimidating and cumbersome.

Basically I want to merge the yellow rows into one column when the ID = 2 but keep the table of values untouched above it after macro is run for each instance of ID =2.

this could easily be done with a macro but what are you trying to merge exactly?
Edit: do you mean if ID = 2, copy & paste the entire row to a new column?

No, I just want to merge the yellow fields in to one field and keep the values in the table above it in same format. This way the very long sentence will show under the column.

Of course my follow up question once this is done will be how do I wrap the text also.

So it looks like this: example.png

Follow-up question.... does the subsequent text get longer, or do you want all "Stuff" text to be equal? when you say MERGE I'm thinking we're merging two or more cells into one but I don't see that from the photo shared.

The "Very long sentence" varies from "N/A" to a very long string and everything in between. I deleted the original string and typed that in instead as dummy data just for the example.

Notice the two images and see how I want to merge the data into one cell but keep table of numeric values in same format.

You never want to "Merge and Center" when working with VBA... you can, however, format the cell height and width and wrap the text within that cell. The only problem with that is the column width will change for all cells in the column

Originally Posted by NerdyDataGuy

Notice the two images and see how I want to merge the data into one cell but keep table of numeric values in same format.

Yes, I see that & I just responded with Merge and Center is not VBA friendly.

I initially thought you wanted to merge two or more strings, which is easy enough to do in VBA.

Oh, I did the merge and center by accident to the numeric values. Disregard that part.

I would like to merge the very long sentence row from "Stuff" column to be Stuff + a+b+c+d+e+f+g columns

so wherever ID = 2 merge those cells and leave the numbers alone.

Yes, I can do that, no problem!

if you had more than 8 columns, I would have looped through the columns, but because its so few I wrote a code that gives a better visual representation of what's happening.

I was trying to figure out why it didnt work and then it made it worse with each attempt.

But when I simply plug in your code to same spreadsheet I get the following image. The value I wanted to merge became 0 and didnt merge the cells.Attachment 781574

try sharing the picture again - I recreated your spreadsheet and it worked for me.... are you running the code on the same sheet?


& take a minute to look at the yellow ribbon at the top, we could work this out much faster with a shared workbook

Attachment 781577 oops see attached.

again, not attached.

Attach via "Go Advanced" at the bottom near the post button & attach from there. For whatever reason, attachments don't work through the basic response window.

Think this worked.

It did work...Thanks. give this a try in the workbook that you shared.

Please Login or Register  to view this content.

Didnt merge the rows as I was hoping.

Originally Posted by NerdyDataGuy

Didnt merge the rows as I was hoping.

Like I said earlier, you really don't want to merge cells in VBA... BUT if you really wanted to do it that way, this code (below) will work:

Please Login or Register  to view this content.

This worked, but how would this work if I moved ID column to the end of this table and included another row at beginning?

See attached.

I don't see the attached item... but to do what you're describing, assuming you meant adding a column and not a row in place of the ID column, you would have to change your references as it relates to the ID column...

for example, instead of offsetting (0,2) for a, you would offset (0,-8). Without seeing the workbook, it would be difficult to change the code accordingly.

Think I forgot to hit upload for the file.

Here you go:

Please add rep w/ the bottom star if I helped you out

Please Login or Register  to view this content.

I was playing with this and trying the get it to work changing some of the numeric values.

Got close but didnt get there.

This code merges all the columns except last into one cell and removes the rest of the data too.