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.

	A	B	C	D
1	Lo!			Lo!
2	This	This	THIS	this
3	is	is	is	is
4	as	as
5	the	the	the	the
6	bread	bread	food	bread
7	of	of	of	of
8	affliction	affliction	affliction	affliction,
9	which	which	which	which
10	our	our	our	our
11	ancestors	forefathers	fathers	ancestors
12			did
13	ate	ate	eat	ate

<tbody>
</tbody>

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:

	A	B	C	D
1	Lo!			Lo!
2	This	This	THIS	this
3	is	is	is	is
4	as	as
5	the	the	the	the
6	bread of affliction	bread of affliction	food of affliction	bread of affliction,
7	which	which	which	which
8	our	our	our	our
9	ancestors	forefathers	fathers	ancestors
10			did
11	ate	ate	eat	ate

<tbody>
</tbody>

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