I am working on writing a VBA script to merge rows if ID's match and then sum the information in a few fields and finally delete the lower row so I only have one entry per ID.
A screen clipping of the data that I'm working with. Rows 13 and 14 are an example of ones that need to be combined.
I based my script off of Raystafarian's answer (latest revision) in the Q&A below:
Sub mergeSumDelete() Dim lastRow As Long Dim myCell As Range 'lastRow = Cells(Rows.Count, "A").End(x1Up).Row 'Alternate way of trying to find the last row since I was having issues with the above lastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row For Each myCell In Range(Cells("A2"), Cells(lastRow, 1)) If (myCell = myCell.Offset(1)) And (myCell.Offset(0, 1) = myCell.Offset(1, 1)) Then 'Add up the data from the matching cells and put it in the top cell myCell.Offset(0, 2) = myCell.Offset(0, 2) + myCell.Offset(1, 2) myCell.Offset(0, 3) = myCell.Offset(0, 3) + myCell.Offset(1, 3) myCell.Offset(0, 4) = myCell.Offset(0, 4) + myCell.Offset(1, 4) 'Delete the bottom row after data is merged myCell.Offset(1).EntireRow.Delete End If Next End Sub
Here's my issues that I'm having.
For the line that is assigning lastRow a value, I am getting a Runtime 1004 error. Not sure what is going on here. I tried doing it a different way and then ran into another error...
I found an alternate way to assign lastRow a value that seemed to work (or didn't throw an error at least..) Now I get an error with the For each statement, a runtime error 5 (Invalid procedure call or argument).