concatenate / merge multiple text cells 1

[paulrbarnard]

I have a large spreadsheet with comments attached to the data in the row. Unfortunatly some previous administrators of the spreadsheet have added additional rows/cols to add extra comments rather than keeping them all in the existing cell. This is a disaster when it comes to sorting the data

Example

1 data1 "comment1, comment2, comment3" - what should be there
2 data2 "comment1" "comment2" "comment3" - what I've got

I have been trying to write a macro that lets me select the required cells then concatenates the text into the active cell. This has so far got me beat. Can anyone help out?

Thanks
Paul

 

[JGillespie]

try this!!

Sub MyMerge()
Dim MyRange As Range
Dim MyStr As String
Dim MyLen As Integer
Set MyRange = Selection
For Each MyCell In MyRange
MyStr = MyStr & ", " & MyCell.Value
MyLen = Len(MyStr)
MyStr = Mid(MyStr, 2, MyLen)
Next
Application.DisplayAlerts = False
Selection.Merge
Selection.Value = MyStr
Application.DisplayAlerts = True
End Sub
Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[paulrbarnard]

Many thanks. Works a treat!