vba - 连接和迭代多个单元格VBA excel

Question

我想迭代存储在不同单元格中的数据(类似于下图所示)，并将它们组合成一个由新行 (chr(10)) 分隔的单元格。 需要导入到一个单元格中的数据量将会发生变化。

2991
19391
423
435
436

无论是否有任何换行符，代码都需要遍历整个工作表。所需格式为:

    2991 - all three cells would be combined into one cell in the next column to this one.
    19391
    423
-Line space, this will need to be taken into account and is the seperator of data.
    26991 - all four cells would be combined into one cell in the next column to this one.
    19331
    424
    6764

下面是我到目前为止所得到的，它获取当前行左侧的列并将其合并，这是错误的。

Sub ConcatColumns()

   Do While ActiveCell <> ""  'Loops until the active cell is blank.

      ActiveCell.Offset(0, 1).FormulaR1C1 = _
         ActiveCell.Offset(0, -1) & chr(10) & ActiveCell.Offset(0, 0)

      ActiveCell.Offset(1, 0).Select
   Loop

End Sub

Answer 1

您可以使用此代码实现上述目的

Sub Main()

    Dim i As Long
    Dim c As Range
    For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1

        Dim strBuilder As String
        Set c = Range("A" & i)

        If Not IsEmpty(c) And i <> 1 Then
            strBuilder = c & Chr(10) & strBuilder
        ElseIf i = 1 Then
            strBuilder = c & Chr(10) & strBuilder
            c.Offset(0, 1) = Left(strBuilder, Len(strBuilder) - 1)
            strBuilder = vbNullString
        Else
            c.Offset(1, 1) = Left(strBuilder, Len(strBuilder) - 1)
            strBuilder = vbNullString
        End If

    Next i

End Sub