PDA

View Full Version : Combine text from different cell in to one of them



blastpwr1970
08-24-2012, 04:26 PM
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)

http://s9.postimage.org/rjqk4p1ez/combine_cells.jpg (http://postimage.org/image/rjqk4p1ez/)

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

Thank you all

p45cal
08-25-2012, 06:31 AM
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.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

blastpwr1970
08-27-2012, 06:44 AM
Hi p45cal, thank you for your help.

blastpwr1970
08-27-2012, 06:48 AM
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.:ipray: Thank you again for your help.

p45cal
08-27-2012, 01:34 PM
it is deleting the cell on the right and on the left and those cell also need to be combine before deleting themBe specific, which cells don't you want deleted (the addresses please)?