在 Excel 中,如何将多行值合并为一行?

在 Excel 中,如何将多行值合并为一行?

嗨,我正在尝试调整之前发布的优秀 VBA,以遍历数据并获取第 1 列中具有相同 ID 的所有行,并将它们全部添加到一行中。尝试从中获取:

起点

对此

终点

但是,即使第 1 列中有两行以上具有相同 ID 的行,下面改编的代码也只能合并 2 行。

Sub CombineInvoices()
Dim currentRow As Long
Dim currentCol As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row 
currentCol = 4
For currentRow = LastRow To 2 Step -1
    If Cells(currentRow, 1) = Cells(currentRow - 1, 1) Then
        Range(Cells(currentRow, 1), Cells(currentRow, 4)).Copy Destination:=Range(Cells(currentRow - 1, currentCol + 1), Cells(currentRow - 1, currentCol + 4))
        Rows(currentRow).EntireRow.Delete
    End If
Next
currentCol = currentCol + 4
End Sub

我真的很感激所有的帮助。

如果你移动 currentCol = currentCol + 4 语句

答案1

成功解决了。我删除了行,然后分开复制和删除语句,如下所示,一切正常!耶!

Sub CombineRowsRevisitedStep()
Dim currentRow As Long
Dim currentCol As Long
Dim LastRow As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
currentCol = 4
For currentRow = LastRow To 2 Step -1
    If Cells(currentRow, 1) = Cells(currentRow - 1, 1) Then
        Range(Cells(currentRow, 1), Cells(currentRow, currentCol)).Copy Destination:=Range(Cells(currentRow - 1, 4), Cells(currentRow - 1, currentCol + 30))
    currentCol = currentCol + 4
    End If
Next
For currentRow = LastRow To 2 Step -1
    If Cells(currentRow, 1) = Cells(currentRow - 1, 1) Then
        Rows(currentRow).EntireRow.Delete
    End If
Next
End Sub

相关内容