Merging Mulitple Rows of Cells

[jasonwills]

Hi All,


i have come across this VBA from someone elses post, and it is very near what i am after.

[pre]

Sub JoinAndMerge2()
Const Delimiter = " "
On Error Resume Next
With Selection
If Selection.Rows.Count > 1 Then
.Item(1).Value = Join(WorksheetFunction.Transpose(Selection), Delimiter)
.Item(2).Resize(Selection.Count–1).Clear
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
Else
.Item(1).Value = Join(WorksheetFunction.Index(Selection.Value, 1, 0), Delimiter)
.Item(2).Resize(1, Selection.Count–1).Clear
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlGeneral
End If
.Merge
.WrapText = True
End With
End Sub

[/pre]
however, what i'd like to be able to do is to select a whole load of rows at one, and have the macro merge each line separately, rather than into 1 large cell.


so if i select cells A1:C3, it will merge A1:3 and B1:3 and C1:3 etc.


and just to add one other thing, i'd also like to apply the macro only if the cell contains a certain word.


any help / pointers is greatly appreciated.


Regards


Jason

 

[bobhc]

Good day jasonwills


Welcome to the forum, please read the green sticky's they will help you in using the forum.


Now for the bad news


Avoid merging cells (at all costs)


Merged cells can help you arrange values in a meaningful way, but they come with problems lots and lots of problems.

For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge. In addition, not all cell formats stick once you unmerge a cell. You can't sort a column with merged cells. You can't even select a single-column range if there's a merged cell in it.

Don't hesitate to use merged cells if you really need them, but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a superior alternative to merging, you still have the look of merged cells but unlike merged cells you will be able to work with the cells such as applying formulas and formats.


To apply you'll need to launch the Alignment group dialog (Ctrl+1) and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


It takes but a moment to do this to cells in a row and then apply down.


THE CHOICE IS YOURS......but merged will come back and bite you and leave you trying to workout why you cannot work/edit the merged cells