Copy from previous row then delete current row and reset row count

[vsimmons]

The issue is after the myrow is delete the row count increments 1 and leaves extra rows.
I think I need to reset the row count after each delete but not sure how that works.

Loop through column A, and move cell value from myrow to previous row
For myRow = 2 To myLastRow
If Cells(myRow, "A") = Cells(myRow - 1, "A") Then
Cells(myRow - 1, "E") = Cells(myRow, "C")
Cells(myRow, "A").EntireRow.Delete


Sample data:

case ID	Referred On Local	Arrived On Local	First Staff Arrival	Last Staff Arrival
17-000003	1/1/17 11:20 AM	1/1/17 12:45 PM	1:25:00	1/4/17 8:30 AM	copy from last row of same case id
17-000003	1/1/17 11:20 AM	1/2/17 8:15 AM	20:55:00		Delete row
17-000003	1/1/17 11:20 AM	1/2/17 3:30 PM	28:10:00		Delete row
17-000003	1/1/17 11:20 AM	1/2/17 4:30 PM	29:10:00		Delete row
17-000003	1/1/17 11:20 AM	1/3/17 12:00 AM	36:40:00		Delete row
17-000003	1/1/17 11:20 AM	1/3/17 8:30 AM	45:10:00		Delete row
17-000003	1/1/17 11:20 AM	1/3/17 11:45 AM	48:25:00		Delete row
17-000003	1/1/17 11:20 AM	1/4/17 2:37 AM	63:17:00		Delete row
17-000003	1/1/17 11:20 AM	1/4/17 8:05 AM	68:45:00		Delete row
17-000003	1/1/17 11:20 AM	1/4/17 8:30 AM	69:10:00		Delete row after copying to previous row
17-000028	1/3/17 2:40 PM	1/3/17 4:30 PM	1:49:31	1/5/17 11:42 PM	copy from last row of same case id
17-000028	1/3/17 2:40 PM	1/4/17 8:00 AM	17:19:31		Delete row
17-000028	1/3/17 2:40 PM	1/4/17 2:00 PM	23:19:31		Delete row
17-000028	1/3/17 2:40 PM	1/4/17 3:00 PM	24:19:31		Delete row
17-000028	1/3/17 2:40 PM	1/5/17 5:00 AM	38:19:31		Delete row
17-000028	1/3/17 2:40 PM	1/5/17 6:00 AM	39:19:31		Delete row
17-000028	1/3/17 2:40 PM	1/5/17 11:42 PM	57:01:31		Delete row after copying to previous row
17-000033	1/4/17 1:50 AM	1/4/17 10:15 AM	8:25:00	1/5/17 10:45 PM	copy from last row of same case id
17-000033	1/4/17 1:50 AM	1/5/17 11:00 AM	33:10:00		Delete row
17-000033	1/4/17 1:50 AM	1/5/17 7:30 PM	41:40:00		Delete row
17-000033	1/4/17 1:50 AM	1/5/17 10:45 PM	44:55:00		Delete row after copying to previous row

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[jmacleary]

Hi there

I think you just need to keep myrow looking at the same row until all the dups are deleted - this code does it I think (untested)

For myRow = 2 To myLastRow
        If Cells(myRow, "A") = Cells(myRow - 1, "A") Then
         Cells(myRow - 1, "E") = Cells(myRow, "C")
           Cells(myRow, "A").EntireRow.Delete
           myRow=myRow-1  
       end If
Next myRow

 

[vsimmons]

Worked perfectly. I love this site and those that are willing to contribute while the rest of us learn. Very much appreciated.