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
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,055
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:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,571
Office Version
2010
Platform
Windows
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]
 

AVRAHAMROOS

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

AVRAHAMROOS

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,571
Office Version
2010
Platform
Windows
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.
 

AVRAHAMROOS

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

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,571
Office Version
2010
Platform
Windows
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.
 

AVRAHAMROOS

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

Forum statistics

Threads
1,085,714
Messages
5,385,414
Members
401,943
Latest member
xvpnkr

Some videos you may like

This Week's Hot Topics

Top