如何使用 VBA 在 Excel 中求和/合并相似的行？

How to SUM / merge similar rows in Excel using VBA?

提问by gime

I'm trying to build a simple macro in VBA for Excel that would SUM [merge] all the rows that have the same name (value in the first columns). So for example

我正在尝试在 VBA 中为 Excel 构建一个简单的宏，它将对所有具有相同名称的行（第一列中的值）求和 [合并]。所以例如

ExampleRowA 1 0 1 1 3 4
ExampleRowA 2 1 2 2 1 0
ExampleRowC 9 7 7 7 2 5

the result should look like this

结果应该是这样的

ExampleRowA 3 1 3 3 4 4
ExampleRowC 9 7 7 7 2 5

We can assume that the rows that need to be merged are not scattered, and can only appear one after another.

我们可以假设需要合并的行不是分散的，只能一一出现。

I did something like this, and it almost works, except I have to run it two times.

我做了这样的事情，它几乎可以工作，除了我必须运行它两次。

LastRow = ActiveSheet.UsedRange.Rows.Count
Set r = ActiveSheet.UsedRange.Resize(1)
With Application.WorksheetFunction


     For iRow = 2 To LastRow
        If Cells(iRow, 1) = Cells(iRow + 1, 1) Then
            LastCol = r(r.Count).Column
            SumCol = LastCol + 1
                For iCol = 2 To SumCol
                Cells(iRow, iCol) = .Sum(Range(Cells(iRow, iCol), Cells(iRow + 1, iCol)))
                Next iCol
            Rows(iRow + 1).Delete
        End If
     Next iRow

End With

I've done some programming in other scripting languages but am new to VB/VBA and don't know the possibilites/limitations of it.

我已经用其他脚本语言完成了一些编程，但我是 VB/VBA 的新手，不知道它的可能性/限制。

In other languages I would probably use arrays but I don't get the way they work here. I can't deny that because of time constraints I prefer to learn by analyzing examples rather than reading a 500+ page VBA Bible.

在其他语言中，我可能会使用数组，但我不明白它们在这里的工作方式。我不能否认，由于时间限制，我更喜欢通过分析示例而不是阅读 500 多页的 VBA 圣经来学习。

回答by chris neilsen

Try somthing like this:

尝试这样的事情：

LastRow = ActiveSheet.UsedRange.Rows.Count
Set r = ActiveSheet.UsedRange.Resize(1)
With Application.WorksheetFunction

    For iRow = LastRow-1 to 2 step -1
        do while Cells(iRow, 1) = Cells(iRow + 1, 1) 
            LastCol = r(r.Count).Column
            SumCol = LastCol + 1
               For iCol = 2 To SumCol
               Cells(iRow, iCol) = .Sum(Range(Cells(iRow, iCol), Cells(iRow + 1, iCol)))
               Next iCol
            Rows(iRow + 1).Delete
        loop
    Next iRow

End With

回答by Ahmad

This code is easier to read and does the job:

这段代码更容易阅读并且可以完成工作：

Sub Macro1()
    Dim ColumnsCount As Integer

    ColumnsCount = ActiveSheet.UsedRange.Columns.Count

    ActiveSheet.UsedRange.Activate


    Do While ActiveCell.Row <= ActiveSheet.UsedRange.Rows.Count
        If ActiveCell.Value = ActiveCell.Offset(1, 0).Value Then
            For i = 1 To ColumnsCount - 1
                ActiveCell.Offset(0, i).Value = ActiveCell.Offset(0, i).Value + ActiveCell.Offset(1, i).Value
            Next
            ActiveCell.Offset(1, 0).EntireRow.Delete shift:=xlShiftUp
        Else
            ActiveCell.Offset(1, 0).Select
        End If
    Loop


End Sub

回答by michael

'if you have to run the macro twice, idk put a 1: on top of your macro and on the bottom put goto 1 'that will run it twice for you 'or you can call it twice in a different macro. up to you

'如果您必须运行该宏两次，idk1: on top of your macro and on the bottom put goto 1 会输入一个 '将为您运行两次'，或者您可以在不同的宏中调用它两次。由你决定

回答by Rahul Bali

For your Problem, you don't need the Macros or any VBA code.

对于您的问题，您不需要宏或任何 VBA 代码。

You can start by opening the sheet with Table containing duplicate values. Follow Steps:

您可以首先打开包含重复值的表格的工作表。按照步骤：

1. Place the Excel Cursor, where you want the merged Data.Then, go to Data Tab > Consolidate.
2. Consolidate Dialog box will appear.
3. Here enter the Table which has the duplicated values and Click add to Add those values.
4. Then, select the row/column, whichever has duplicate values. In this its the left column.
5. Just Click OK and you are done.

1. 将 Excel 光标放在您想要合并数据的位置。然后，转到数据选项卡 > 合并。
2. 将出现合并对话框。
3. 在此处输入具有重复值的表，然后单击添加以添加这些值。
4. 然后，选择具有重复值的行/列。在这个它的左列。
5. 只需单击“确定”即可完成。