Excel VBA: Combine Row Data of Specific Accounts

[unknownymous]

Hi Gurus,

Good day!

I just want to consult if there's a VBA way to combine the data (get the sum) of specific accounts?


For example I have long list of data but need to combine the total data for Math & Science. Note that there are negative numbers sometimes and Reference 01 is the main account.

Initial Data:

Reference	Name	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6
01	Math	100	75	60	90	95	-100
02	Science	80	75	80	90	-85	80

Output Data: Since Reference No 1 is the main account, this is the surviving account after merging. The name will be renamed as Math & Science after combining the data. Afterwards, this row will be highlighted in yellow cell for easy tracking of this account.

Reference	Name	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6
01	Math & Science	180	150	140	180	10	-20

Appreciate the help.

Thanks!

 

[Kaper]

select both rows of data and run such code:

Sub test()

Dim i As Long
If Selection.Rows.Count <> 2 Or Selection.Columns.Count < 3 Then
  MsgBox "select at least 3 columns and 2 consecutive rows"
  Exit Sub
End If
Selection.Cells(1, 2) = Selection.Cells(1, 2) & " & " & Selection.Cells(2, 2)
For i = 3 To Selection.Columns.Count
  Selection.Cells(1, i) = Selection.Cells(1, i) + Selection.Cells(2, i)
Next i
Selection.Cells(2, 1).Resize(1, Selection.Columns.Count).ClearContents ' one could use here:   Selection.Cells(2, 1).EntireRow.Delete Shift:=xlUp  

End Sub

 

[unknownymous]

select both rows of data and run such code:

Sub test()

Dim i As Long
If Selection.Rows.Count <> 2 Or Selection.Columns.Count < 3 Then
  MsgBox "select at least 3 columns and 2 consecutive rows"
  Exit Sub
End If
Selection.Cells(1, 2) = Selection.Cells(1, 2) & " & " & Selection.Cells(2, 2)
For i = 3 To Selection.Columns.Count
  Selection.Cells(1, i) = Selection.Cells(1, i) + Selection.Cells(2, i)
Next i
Selection.Cells(2, 1).Resize(1, Selection.Columns.Count).ClearContents ' one could use here:   Selection.Cells(2, 1).EntireRow.Delete Shift:=xlUp 

End Sub

Hi Kaper,

Thanks a lot for sharing which works however, I need to do the same for the next 5 tab sheets. I was thinking of looping it but have some questions:

1. If the rows to merge is permanently settled in the Row 2 & 3, can we do that instead of selecting rows?
2. If I want to merge the data up to Column H only, which part of the code are you going to edit?

Appreciate the help as always.