2017-06-14

EXCEL - Macro/sub to join text cells

Sub sJoinCells()

    temp = ""
    rngStart = Selection.Cells(1, 1).Address
    rngEnd = Selection.Cells(Selection.Rows.Count, Selection.Columns.Count).Address
    firstRowNumber = getRow(rngStart)
    lastRowNumber = getRow(rngEnd)
    rowCount = lastRowNumber - firstRowNumber
    Debug.Print firstRowNumber & " to " & lastRowNumber & " (rowCount: " & rowCount & ")"

    ' Validation: exit if only one cell selected
    If rowCount < 1 Then
        Debug.Print "sJoinCells: Single cell selected, nothing to do."
        Exit Sub
    End If

    ' Accumulate the values
    For i = 1 To Selection.Rows.Count

        ' Cell value cleanup
        v = Trim(Selection.Rows(i).Value)

        ' If the cum value does not end with a period, then make sure
        ' the first letter of this cell is NOT capitalized
        If Left(temp, 1) <> "." Then
            v = lower(Left(v, 1)) & Right(v, Len(v) - 1)
        End If

        ' Accumulate
        temp = temp & v & " "
    Next i

    ' Set concatenated values on first row
    Debug.Print "temp: " & temp
    'Selection.Rows(1).Value = temp

    ' Delete rows
    'Debug.Print (firstRowNumber + 1) & ":" & lastRowNumber
    'Rows((firstRowNumber + 1) & ":" & lastRowNumber).EntireRow.Delete
 
End Sub




No comments:

Post a Comment