Combine text from different cell in to one of them

[blastpwr1970]

Hi all, I hope somebody can help me.
I need to combine some text from different cells in to the first cell in the range but there are some merge cell at the front and back of the text to be merge in to the one cell.

A picture is worth a thousand words:

Just click on picture to zoom (will open in new tab)



I included an image and the excel file to further explain.

Thank you all

[p45cal]

Select a range of cells which includes the rows you want to process (eg. cells A1:A17 in your example, though how many and which columns you choose doesn't matter; the code only looks at the rows of your selection). Run the following macro. It uses the the count of the rows of each merged area in column A to determine what to concatenate.[VBA]Sub blah()
'http://vbaexpress.com/forum/showthread.php?t=43433
RwOne = Selection.Row
RwLast = RwOne + Selection.Rows.Count - 1
For i = RwLast To RwOne Step -1
Set xx = Cells(i, 1).MergeArea
RowsInMergeArea = xx.Rows.Count
If RowsInMergeArea > 1 Then
For Each colm In xx.Offset(, 3).Resize(RowsInMergeArea, 2).Columns
colm.Cells(1) = Join(Application.Transpose(colm.Value), " ")
Next colm
xx.Resize(RowsInMergeArea - 1).Offset(1).EntireRow.Delete
i = i - RowsInMergeArea + 1
End If
Next i
Selection.Cells(1).Select
End Sub
[/VBA]

[blastpwr1970]

Hi p45cal, thank you for your help.

[blastpwr1970]

The code works great but there is one problem it is deleting the cell on the right and on the left and those cell also need to be combine before deleting them. Thank you again for your help.

[p45cal]

it is deleting the cell on the right and on the left and those cell also need to be combine before deleting them

Be specific, which cells don't you want deleted (the addresses please)?