Hi All,
I am quite stuck with the below.
I have four columns A-D, e.g.:
A | B | C | D |
Joe | ZZ | 4 | X |
Tom | YY | 5 | X |
Aida | YY | 2 | X |
Tom | ZZ | 2 | X |
Joe | ZZ | 1 | X |
Joe | YY | 6 | X |
Aida | YY | 0 | X |
Tom | ZZ | 7 | X |
Joe | YY | 4 | X |
I want to merge rows for which info in both columns A and B are identical, add for those rows values in column C and retain info from column D untouched (column D is irrelevant for this problem but including it here just to be consistent with my range). So my final product will look:
A | B | C | D |
Joe | ZZ | 5 | X |
Tom | YY | 14 | X |
Aida | YY | 2 | X |
Joe | YY | 10 | X |
I also want to retain both of my lists - the original and the merged one.
To achieve the above, I have written some VBA code:
Private Sub CommandButton1_Click()
ActiveSheet.Range("A" & i & ":D" & i).Copy
ActiveSheet.Range("F" & i & ":I" & i).Select
ActiveSheet.Paste
For Each a In Range("F1", Cells(Rows.Count, "F").End(xlUp))
For r = 1 To Cells(Rows.Count, "F").End(xlUp).Row - a.Row
If a = a.Offset(r, 0) And a.Offset(0, 1) = a.Offset(r, 1) Then
a.Offset(0, 2) = a.Offset(0, 2) + a.Offset(r, 2)
a.Offset(r, 0).EntireRow.Delete
r = r - 1
End If
Next r
Next a
End Sub
Display More
First, I want to copy my original list from columns A:D to columns F:I and then I want the operations to be performed on the F:I range only.
What my code does it is copies the columns over but still performs the merging and adding the values on both ranges (the original data and the copied data). I am therefore left with two ranges side by side, both of which are merged and I lose my original data.
I am very new to VBA and cannot see a reason why it would do that. I've tried looking through posts for solutions but nothing answered my questions so far. I apologise in advance if the code is not the best but it's my first steps.
I would be extremely grateful if you could point me in the right direction.