将多个行(一个col)合并为一个(最后一个)行，并删除前面的行

Question

我创建了一个Excel宏，目的是将所有选定的多行的内容合并到最后一行，如下代码:

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

问题是，如何删除所有这些多个(整个)行，除了最后一行?

Answer 1

你可以试试这个

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