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

vsimmons

New Member
Joined
Sep 27, 2017
Messages
3
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 IDReferred On LocalArrived On LocalFirst Staff ArrivalLast Staff Arrival
17-0000031/1/17 11:20 AM1/1/17 12:45 PM1:25:001/4/17 8:30 AMcopy from last row of same case id
17-0000031/1/17 11:20 AM1/2/17 8:15 AM20:55:00 Delete row
17-0000031/1/17 11:20 AM1/2/17 3:30 PM28:10:00 Delete row
17-0000031/1/17 11:20 AM1/2/17 4:30 PM29:10:00 Delete row
17-0000031/1/17 11:20 AM1/3/17 12:00 AM36:40:00 Delete row
17-0000031/1/17 11:20 AM1/3/17 8:30 AM45:10:00 Delete row
17-0000031/1/17 11:20 AM1/3/17 11:45 AM48:25:00 Delete row
17-0000031/1/17 11:20 AM1/4/17 2:37 AM63:17:00 Delete row
17-0000031/1/17 11:20 AM1/4/17 8:05 AM68:45:00 Delete row
17-0000031/1/17 11:20 AM1/4/17 8:30 AM69:10:00 Delete row after copying to previous row
17-0000281/3/17 2:40 PM1/3/17 4:30 PM1:49:311/5/17 11:42 PMcopy from last row of same case id
17-0000281/3/17 2:40 PM1/4/17 8:00 AM17:19:31 Delete row
17-0000281/3/17 2:40 PM1/4/17 2:00 PM23:19:31 Delete row
17-0000281/3/17 2:40 PM1/4/17 3:00 PM24:19:31 Delete row
17-0000281/3/17 2:40 PM1/5/17 5:00 AM38:19:31 Delete row
17-0000281/3/17 2:40 PM1/5/17 6:00 AM39:19:31 Delete row
17-0000281/3/17 2:40 PM1/5/17 11:42 PM57:01:31 Delete row after copying to previous row
17-0000331/4/17 1:50 AM1/4/17 10:15 AM8:25:001/5/17 10:45 PMcopy from last row of same case id
17-0000331/4/17 1:50 AM1/5/17 11:00 AM33:10:00 Delete row
17-0000331/4/17 1:50 AM1/5/17 7:30 PM41:40:00 Delete row
17-0000331/4/17 1:50 AM1/5/17 10:45 PM44:55:00 Delete row after copying to previous row

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

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
1,054
Office Version
  1. 365
  2. 2007
Platform
  1. Windows
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)

Code:
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
 
Last edited:

vsimmons

New Member
Joined
Sep 27, 2017
Messages
3
Worked perfectly. I love this site and those that are willing to contribute while the rest of us learn. Very much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,152
Messages
5,600,017
Members
414,356
Latest member
death20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top