http://www.excelforum.com/excel-prog...ate-macro.html
Changes:
Macro breaks if range (rngA) has only 1 line of data
Change concatenate to populate in ,9 instead of ,6
Concatenate eliminate * and - (astricks and dashes)
Change =LEN formula to populate in ,10 instead of ,8
Have number from rngA populate in ,8 without the dash
Sub Concat_Long_Text()
Dim rngA As Range
For Each rngA In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
If Application.CountA(rngA.Offset(, 6)) > 0 Then
rngA(rngA.Count + 1, 6).Value = Join(Application.Transpose(rngA.Offset(, 6)))
rngA(rngA.Count + 1, 8).FormulaR1C1 = "=LEN(RC[-2])"
End If
Next
End Sub
Bookmarks