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

Question

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.

Does the solution need to be a macro? Have you considered using Excel's built-in spreadsheet capabilities instead? For example, if the possible values for the first column are known ahead of time, you could do this very easily using the SUMIF worksheet function. If the possible values for the first column are not known ahead of time, you could use a pivot table. — Brian Camire
– Brian Camire, Commented Jun 20, 2011 at 13:19

chris neilsen · Accepted Answer · 2011-06-20 10:23:18Z

3

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

answered Jun 20, 2011 at 10:23

chris neilsen

53.3k10 gold badges91 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gime Over a year ago

Thanks! This solves iteration problems perfectly. But I'm still open for a more solid solution (if there is any).

Jon Crowell · Accepted Answer · 2012-05-28 07:10:20Z

2

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

edited May 28, 2012 at 7:10

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

answered Jun 20, 2011 at 11:36

Ahmad

12.8k7 gold badges53 silver badges98 bronze badges

1 Comment

Brian Over a year ago

Is there any way to do this if the rows you want to combine are not next to each other?

michael · Accepted Answer · 2015-07-09 20:27:56Z

0

'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

answered Jul 9, 2015 at 20:27

michael

1

1 Comment

Greg Viers Over a year ago

Won't that generate an infinite loop?

Rahul Bali · Accepted Answer · 2015-10-08 10:49:31Z

0

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

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

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

edited Oct 8, 2015 at 10:49

answered Oct 8, 2015 at 9:10

Rahul Bali

83212 silver badges29 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related