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

问题描述

我试图在VBA for Excel中构建一个简单的宏，将SUM [合并]所有具有相同名称的行（第一列中的值）。
所以例如

  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 
  pre> 
 
 
我们可以假设需要合并的行不会分散，只能一个接一个出现。
 
 
 / p> 
 
 
  LastRow = ActiveSheet.UsedRange.Rows.Count 
设置r = ActiveSheet.UsedRange.Resize（1）
与Application.WorksheetFunction 
 
 
对于iRow = 2 To LastRow 
如果Cells（iRow，1）= Cells（iRow + 1，1）然后
 LastCol = r（r.Count）.Column 
 SumCol = LastCol + 1 
对于iCol = 2到SumCol 
 Cells（iRow，iCol）= .Sum（Range（Cells（iRow，iCol），Cells（iRow + 1，iCol））） 
下一个iCol 
行（iRow + 1）.Delete 
结束如果
下一步iRow 
 
结束
  
我已经用其他脚本语言编写了一些编程，但是VB / VBA是新的，不知道它的可能性/限制。 p> 
 
 
在其他语言中，我可能会使用数组，但我没有得到他们在这里工作的方式。我不能否认，因为时间限制，我更喜欢通过分析实例来学习，而不是阅读一个500多页的VBA圣经。
解决方案
 p>尝试这样的事情：
  LastRow = ActiveSheet.UsedRange.Rows.Count 
设置r = ActiveSheet.UsedRange .Resize（1）
 With Application.WorksheetFunction 
 
对于iRow = LastRow-1到2 step -1 
做while Cells（iRow，1）= Cells（iRow + 1 ，1）
 LastCol = r（r.Count）.Column 
 SumCol = LastCol + 1 
对于iCol = 2 To SumCol 
单元格（iRow，iCol）= .Sum范围（单元格（iRow，iCol），单元格（iRow + 1，iCol）））
下一步iCol 
行（iRow + 1）.Delete 
循环
下一步iRow 
 
结束
  
 
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
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.

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.
解决方案
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


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