Merging multiple cells with content

[AVRAHAMROOS]

I haven't been here for a long time but you guys have helped me in the past immensely when I was building an excel based text analysis tool (see https://www.academia.edu/30391368/PDD_Poetic_Devices_Detector_)

I am now busy with something else text based and need more help.

Given a table with several similar texts in each column - one word per cell e.g.

[TABLE="width: 283"]
<tbody>[TR]
[TD][/TD]
[TD="align: left"]A[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]Lo![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Lo![/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]This[/TD]
[TD="align: left"]This[/TD]
[TD="align: left"]THIS[/TD]
[TD="align: left"]this[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: left"]is[/TD]
[TD="align: left"]is[/TD]
[TD="align: left"]is[/TD]
[TD="align: left"]is[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: left"]as[/TD]
[TD="align: left"]as[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: left"]the[/TD]
[TD="align: left"]the[/TD]
[TD="align: left"]the[/TD]
[TD="align: left"]the[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: left"]bread[/TD]
[TD="align: left"]bread[/TD]
[TD="align: left"]food[/TD]
[TD="align: left"]bread[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: left"]of[/TD]
[TD="align: left"]of[/TD]
[TD="align: left"]of[/TD]
[TD="align: left"]of[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: left"]affliction[/TD]
[TD="align: left"]affliction[/TD]
[TD="align: left"]affliction[/TD]
[TD="align: left"]affliction,[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: left"]which[/TD]
[TD="align: left"]which[/TD]
[TD="align: left"]which[/TD]
[TD="align: left"]which[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: left"]our[/TD]
[TD="align: left"]our[/TD]
[TD="align: left"]our[/TD]
[TD="align: left"]our[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: left"]ancestors[/TD]
[TD="align: left"]forefathers[/TD]
[TD="align: left"]fathers[/TD]
[TD="align: left"]ancestors[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]did[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: left"]ate[/TD]
[TD="align: left"]ate[/TD]
[TD="align: left"]eat[/TD]
[TD="align: left"]ate[/TD]
[/TR]
</tbody>[/TABLE]

I need a way to merge certain cells vertically while keeping all text and do this simultaneously for each text version (=column). E.g. How do I merge "bread of affliction" (or "food of affliction") in rows 6-8 for columns A, B, C, D?
Ideally, it should be done by highlighting rows 6-8 and hitting a macro/ button

The end result should look like this:

[TABLE="width: 449"]
<tbody>[TR]
[TD][/TD]
[TD="align: left"]A[/TD]
[TD="align: left"]B[/TD]
[TD="align: left"]C[/TD]
[TD="align: left"]D[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: left"]Lo![/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]Lo![/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: left"]This[/TD]
[TD="align: left"]This[/TD]
[TD="align: left"]THIS[/TD]
[TD="align: left"]this[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: left"]is[/TD]
[TD="align: left"]is[/TD]
[TD="align: left"]is[/TD]
[TD="align: left"]is[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: left"]as[/TD]
[TD="align: left"]as[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: left"]the[/TD]
[TD="align: left"]the[/TD]
[TD="align: left"]the[/TD]
[TD="align: left"]the[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: left"]bread of affliction[/TD]
[TD="align: left"]bread of affliction[/TD]
[TD="align: left"]food of affliction[/TD]
[TD="align: left"]bread of affliction,[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: left"]which[/TD]
[TD="align: left"]which[/TD]
[TD="align: left"]which[/TD]
[TD="align: left"]which[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: left"]our[/TD]
[TD="align: left"]our[/TD]
[TD="align: left"]our[/TD]
[TD="align: left"]our[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: left"]ancestors[/TD]
[TD="align: left"]forefathers[/TD]
[TD="align: left"]fathers[/TD]
[TD="align: left"]ancestors[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: left"]did[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: left"]ate[/TD]
[TD="align: left"]ate[/TD]
[TD="align: left"]eat[/TD]
[TD="align: left"]ate[/TD]
[/TR]
</tbody>[/TABLE]

Thanking you in advance,

Avraham

 

[Rick Rothstein]

At first I tried to run the macro with just a few numbers in the cells. I now changed the numbers to words and this time I get the following error message:

Can't execute code in break mode.

That means your code did not end, it is still running but only temporarily stopped at a breakpoint. Either click the small square icon in the "ribbon" under the menu bar or, alternately, click the Run menu bar item and then click the Reset item in the dropdown menu that appears to fully halt the program.

The highlighted line in the code is indeed <tbody>[TR]

I am not sure what you are doing, but <tbody.[TR] is not VBA code (looks more like HTML tag information to me)... if you actually have that in your code, delete it as that would not be syntactically correct.

Does this help or should I upload the file?

If doing the above doesn't make the code work correctly, then yes, post the workbook to DropBox and let us see exactly what you have.

 

[AVRAHAMROOS]

link to file:

test.xlsm

Shared with Dropbox

www.dropbox.com

lines I try to merge: 2-6

error message: Can't execute code in break mode

highlighted line in the code: <tbody>[TR]

 

[Rick Rothstein]

I explained how to handle the Break Mode warning in Message #11.

As to your code, I was right... you have HTML tags within it which are not part of the VBA code structure. Here is how your code should look...

Sub test1()
  Dim C As Variant
  For Each C In Intersect(ActiveSheet.UsedRange, Selection).Columns
    Cells(Selection(1).Row, C.Column) = Join(Application.Transpose(Intersect(Selection, C)))
  Next
  Selection(1).Offset(1).Resize(Selection.Rows.Count - 1).EntireRow.Delete
End Sub

In the future, when you take code from this forum, copy it from the forum message directly... do not click the Reply button and try to copy it from there as it has the HTML tags that tell the forum message how to display the code.

 

[AVRAHAMROOS]

Great! This works. Thanks a lot