Cut and Move Specific Row then Delete Duplicate row

Papi

Well-known Member
The workbook has three worksheets called HR, Data and Past. I need to have the cursor on a row in the HR worksheet and be able to cut that row, move the entire row to the Past worksheet (last row if possible) and then delete the blank row from HR and then find the same row in the Data worksheet matching the company name (unique identifier) that was moved, delete that row and return to the HR worksheet with the cursor on the row just below the one deleted. The above will be done on a one as basis.

Thanks

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
The workbook has three worksheets called HR, Data and Past. I need to have the cursor on a row in the HR worksheet and be able to cut that row, move the entire row to the Past worksheet (last row if possible) and then delete the blank row from HR and then find the same row in the Data worksheet matching the company name (unique identifier) that was moved, delete that row and return to the HR worksheet with the cursor on the row just below the one deleted. The above will be done on a one as basis.

Thanks

I'm guessing that the company name is in column D, Right? For both Data and Past!

Yes, they are in the same columns. Thanks.

Yes, they are in the same columns. Thanks.

Well I used column C, If that is not the correct column, you will need to make the corrections where noted.

Code:
``````Sub cutnDel()
Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, c As Range
Set sh1 = Sheets("HR")
Set sh2 = Sheets("Past")
Set sh3 = Sheets("Data")
Selection.EntireRow.Cut sh2.Cells(Rows.Count, 1).End(xlUp)(2)
Set c = sh3.Range("[COLOR=#b22222]C:C[/COLOR]").Find(sh2.Cells(Rows.Count, [COLOR=#b22222]3[/COLOR]).End(xlUp).Value, LookIn:=xlValues) 'If not col C, make changes here.
If Not c Is Nothing Then c.EntireRow.Delete
Selection.EntireRow.Delete
End Sub``````

Last edited:
Thanks JLGWhiz,

Thanks for your efforts. I am running this to test but it is not doing what I expected. I will reply back in a few minutes.

What the macro is doing is copying and pasting the row of the HR worksheet to the bottom of the same worksheet. What I need it to do is copy the entire row to the last known row of the third worksheet which is the Past worksheet and then delete the data in the second worksheet called Data when it finds the name in column C matching the item moved and then delete that row. Does this make sense? I just noticed that my sheets 2 and 3 are reversed to the way you have them.

What the macro is doing is copying and pasting the row of the HR worksheet to the bottom of the same worksheet. What I need it to do is copy the entire row to the last known row of the third worksheet which is the Past worksheet and then delete the data in the second worksheet called Data when it finds the name in column C matching the item moved and then delete that row. Does this make sense? I just noticed that my sheets 2 and 3 are reversed to the way you have them.

Don't know how those results happened, it worked as described when I tested it. However try this code without the use of the object variables. See if it does what you want.
Code:
``````Sub cutnDel2()
Selection.EntireRow.Cut Sheets("Past").Cells(Rows.Count, 1).End(xlUp)(2)
Set c = Sheets("Data").Range("C:C").Find(Sheets("Past").Cells(Rows.Count, 3).End(xlUp).Value, LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Selection.EntireRow.Delete
End Sub``````

Hello JLGWhiz,

That is perfect! Thanks for all your time and effort. A great piece of work.

Don't know how those results happened, it worked as described when I tested it. However try this code without the use of the object variables. See if it does what you want.
Code:
``````Sub cutnDel2()
Selection.EntireRow.Cut Sheets("Past").Cells(Rows.Count, 1).End(xlUp)(2)
Set c = Sheets("Data").Range("C:C").Find(Sheets("Past").Cells(Rows.Count, 3).End(xlUp).Value, LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Selection.EntireRow.Delete
End Sub``````

If I need to reverse the above by returning one selected row from "Past" (Selection.EntireRow.Cut) into two worksheets called "Data" and "Main" how would I go about that so the "Past" row is cut and pasted as noted with the cursor ending on Row 2 of the "Main" worksheet
The difference is that rather than delete the same row from "Data" that it pastes the same row in "Data" and "Main" between row 2 and 3. I'm not sure how to get the command ""Selection.EntireRow.Cut Sheets("Past").Cells(Rows.Count, 1).End(xlUp)(2)" to paste in two worksheets.

Replies
6
Views
405
Replies
16
Views
587
Replies
1
Views
180
Replies
5
Views
519
Replies
3
Views
182

1,196,340
Messages
6,014,712
Members
441,838
Latest member
ykg1991

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.

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

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