如何使用 VBA 在 Excel 中求和/合并相似的行? [英] How to SUM / merge similar rows in Excel using VBA?

问题描述

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

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

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

结果应该是这样的

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

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

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

在其他语言中，我可能会使用数组，但我不明白它们在这里的工作方式.我不能否认，由于时间限制，我更喜欢通过分析例子来学习，而不是阅读 500 多页的 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.

推荐答案

尝试这样的事情:

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

这篇关于如何使用 VBA 在 Excel 中求和/合并相似的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！