Merging multiple cells with content

A

AVRAHAMROOS

New Member
Joined
Nov 24, 2016
Messages
21
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Hi,

Try this, tested ok.

Code: 
Sub mergeselected()


Dim myrow As Long
Dim norows As Long
Dim mystring As String
Dim mycol As Long
Dim sht As Worksheet
Dim lastcolumn As Long


Set sht = ActiveSheet
myrow = ActiveCell.Row
norows = Selection.Rows.Count
nocol = Selection.Columns.Count
lastcolumn = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
mycol = 1


Rows(myrow).Insert


Do Until mycol = lastcolumn + 1
myrow = ActiveCell.Row
mystring = ""
For Each Row In Selection.Rows
If Cells(myrow + 1, mycol).Value <> "" Then
mystring = mystring & Cells(myrow + 1, mycol).Value & " "
End If
myrow = myrow + 1
Next Row
Cells(myrow - norows, mycol).Value = mystring
mycol = mycol + 1
Loop


ActiveCell.EntireRow.Font.Bold = True


End Sub
 
Last edited:
Upvote 0
Here is another macro that should also work...
Code: 
[table="width: 500"]
[tr]
	[td]Sub MergeSelected()
  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[/td]
[/tr]
[/table]
 
Upvote 0
Rick Rothstein said:
Here is another macro that should also work...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MergeSelected()
  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[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...


I like this one because it also deletes the now superfluous cells. Great. This reallt helps.
 
Upvote 0
This used to work in the past but now I get a compile error, syntax error.

[table="width: 500"]
[tr]
[td]Sub MergeSelected()
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[/td]
[/tr]
[/table]

Can anyone explain why?
 
Upvote 0
What line is highlighted when the error occurs (if you can't tell, click the Debug button on the error message first)?

Also...

Did you remember to make a selection first, before you ran the code?

Did you change versions of Excel? If so, from which version to which version?

Did you add new VBA code to the workbook before it stopped working? If so, post it.
 
Upvote 0
Dear Rick,

Thank you so much for getting back to me on this.

1. Yes, I make a selection first
2. Yes, my computer crashed and a new office package was installed. I do not know what the former version was. At present I am using Microsoft Office Professional Plus 2016 Excel MSO 64bit.
3. I am trying out this code in a completely new workbook that has nothing else in it except a few populated cells and the macro.
4. The line highlighted is: <tbody>[TR]
 
Upvote 0
Something went wrong with your #4 answer... all I see is <tbody>[TR] ?

Can you post a copy of that "new workbook" to DropBox, share it and then give us the link to the file that they give you to distribute? Also tell us the cell range you select to get the error you describe. That way, we can duplicate exactly what you are doing and seeing.
 
Upvote 0
I will if the following doesn't give you more information:

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.

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

Does this help or should I upload the file?
 
Upvote 0
You must log in or register to reply here.

Similar threads

gtd526
  • Solved
vba select a cell that contains certain text
Replies
4
Views
783
gtd526
gtd526
E
  • Solved
Return Contents of Cell Based on Column Number
Replies
5
Views
340
Fluff
Fluff
ExcelNewbie2020
  • Question Question
filter values to a merge cells
Replies
1
Views
307
arthurbr
arthurbr
ExcelNewbie2020
  • Solved
merge and allign the sentence in a cell
Replies
4
Views
328
ExcelNewbie2020
ExcelNewbie2020
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
288
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top