CODEWITHSUNDEEP
excelvba
aries.wandari
aries.wandari

Reputation: 97

Merge multiple row (one col) into one (last) row, and delete previous rows

I created an Excel macro intended to merge the content of all selected multiple rows one col into last row, as code below:

Sub asdf()

    Dim rCell As Range
    Dim rRng As Range
    Dim rslt As String
    Dim lastCell As Range
    Dim i As Integer

    Set rRng = Range(Selection.Address)

    For Each rCell In rRng.Cells
        Debug.Print rCell.Address, rCell.Value
        rslt = rslt & " " & rCell.Value
        rslt = Trim(rslt)
        rCell.Value = rslt
    Next rCell

End Sub

The question is, how to delete all those multiple (whole) rows except the last row?

Upvotes: 1

Views: 54

Answers (1)

Stanley
Stanley

Reputation: 2816

You can try this

Sub asdf()
    Dim delCell as Range
    Dim rCell As Range
    Dim rRng As Range
    Dim rslt As String
    Dim lastCell As Range
    Dim i As Integer

    Set rRng = Range(Selection.Address)

    For Each rCell In rRng.Cells
        Debug.Print rCell.Address, rCell.Value
        rslt = rslt & " " & rCell.Value
        rslt = Trim(rslt)
        If Not rCell.Row = rRng.Row + rRng.Rows.Count - 1 Then
           If delCell is Nothing then
             set delCell = rCell
           Else
             set delcell = union(delCell, rCell)
           End If
        End If
        rCell.Value = rslt
    Next rCell
    delCell.EntireRow.Delete
End Sub

Upvotes: 1

Related Questions