Cinema
11-04-2016, 03:52 AM
Hi,
I want to combine (sum up) all the rows that have the same name (value in the first columns).
I found a macro that does the job but it assumes that the rows that need to be merged are not scattered, and can only appear one after another.
How to write this macro for scattered rows?
Sub Combine()
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
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
End Sub
I want to combine (sum up) all the rows that have the same name (value in the first columns).
I found a macro that does the job but it assumes that the rows that need to be merged are not scattered, and can only appear one after another.
How to write this macro for scattered rows?
Sub Combine()
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
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
End Sub