Changes to Concatenate Macro

[mcranda]

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

[GC Excel]

Hello,

See if this works:

Sub Concat_Long_Text2()
    Dim rngA As Range, c As Range

    For Each rngA In Range("A2", Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas

        If Application.CountA(rngA.Offset(, 6)) > 1 Then
            rngA(rngA.Count + 1, 9).Value = Replace(Replace(Join(Application.Transpose(rngA.Offset(, 6))), "*", ""), "-", "")
            rngA(rngA.Count + 1, 10).FormulaR1C1 = "=LEN(RC[-1])"

            For Each c In rngA.Resize(, 1)
                c.Offset(, 7) = "'" & Replace(c, "-", "")
            Next c

        ElseIf Application.CountA(rngA.Offset(, 6)) = 1 Then
            rngA(rngA.Count + 1, 9).Value = Replace(Replace(rngA.Offset(, 6), "*", ""), "-", "")
            rngA(rngA.Count + 1, 10).FormulaR1C1 = "=LEN(RC[-1])"

            For Each c In rngA.Resize(, 1)
                c.Offset(, 7) = "'" & Replace(c, "-", "")
            Next c

        End If

    Next
End Sub