Merging multiple cells with content

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.

ABCD
1Lo!Lo!
2ThisThisTHISthis
3isisisis
4asas
5thethethethe
6breadbreadfoodbread
7ofofofof
8afflictionafflictionafflictionaffliction,
9whichwhichwhichwhich
10ourourourour
11ancestorsforefathersfathersancestors
12did
13ateateeatate

<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:

ABCD
1Lo!Lo!
2ThisThisTHISthis
3isisisis
4asas
5thethethethe
6bread of afflictionbread of afflictionfood of afflictionbread of affliction,
7whichwhichwhichwhich
8ourourourour
9ancestorsforefathersfathersancestors
10did
11ateateeatate

<tbody>
</tbody>

Thanking you in advance,

Avraham
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
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]


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.

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

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

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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