try this macro - limited at this time to 20 sets of string to
concatenate
Sub Macro1()
'Sheet1 has the raw, unconcatenated data
'Sheet2 gets the concatenated data
'istring(i) are the concatenated strings
Dim istring(20) As String
erow = 0
i = 1
start:
erow = erow + 1
Set rng = Sheets("sheet1").Range(Cells(erow, 1), Cells(1000,
1)).Find("For Example:")
If rng Is Nothing Then GoTo done
irow = rng.Row
erow = Cells(irow, 1).End(xlDown).Row
istring(i) = "For example: "
For j = irow To erow
istring(i) = istring(i) & Cells(j, 1).Value
Next j
i = i + 1
If i = 5 Then GoTo done
GoTo start
done:
Sheets("sheet2").Select
Cells(1, 1) = "Example"
For k = 1 To i - 1
Cells(k + 1, 1) = istring(k)
Next k
End Sub
--
duane
------------------------------------------------------------------------
duane's Profile:
http://www.excelforum.com/member.php...o&userid=11624
View this thread:
http://www.excelforum.com/showthread...hreadid=374110