EntireRow.Delete seems to delete incorrect row

Question

I have the following code:

Sub test()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim r As Long
    Dim rng As Range

    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")

    'Set some dummy data
    For r = 1 To 20
        ws1.Cells(r, "A").Value = r + 100
        ws2.Cells(r, "A").Value = r + 200
    Next

    'Set a reference to Sheet1!A11
    Set rng = ws1.Range("A11")

    'Copy Sheet1 row 11 to Sheet2 row 5
    rng.EntireRow.Cut ws2.Range("A5")

    'Delete Sheet1 row 11
    rng.EntireRow.Delete

End Sub

I assign a reference to a cell in row 11 of "Sheet1". Using that reference, I Cut that row to a location on "Sheet2", and then I use the reference to delete the entire row from "Sheet1".

My expectation is that row 11 of "Sheet1" will be deleted, leaving values in column A of 101 to 110 in rows 1 to 10, then 112 to 120 in rows 11 to 19.

What is happening is that row 11 is being cleared (due to the Cut) and row 5 is being deleted.

Image of sheets after code has executed:

The fact that it is row 5 that is deleted appears to be related to the fact that row 5 is the destination row on "Sheet2" of the Cut statement. And if it had deleted row 5 on "Sheet2", I could understand what was happening - i.e. that the reference was being repointed to the other sheet as part of the Cut - but it deletes row 5 on "Sheet1".

Can anyone explain this weird behaviour?

Note: This question was inspired by my attempts to write this answer, and my many comments to the OP of that question telling them "of course EntireRow.Delete will delete your row - why are you telling me it isn't working".

Answer 1

use "copy" instead of "cut"

rng.EntireRow.Copy ws2.Range("A5")

the line at sheet1 A11 does not have to be empty when you delete it

here is a simpler version that shows the undesired behaviour

Sub test()

    Dim rng As Range

    Set rng = Sheets("Sheet1").Range("A11")
    rng.Value = "cut and paste me"

    Sheets("Sheet1").Range("A11").Cut Sheets("Sheet2").Range("A5")  ' rng object not used in this line

    rng.Select     ' selects Sheet1 A5  ...  it should be Sheet2 A5

End Sub

it appears to be a bug in excel

since the cut/paste effectively moves the cut range, excel moves the reference to the new location

excel moves the reference to the correct cell, but it fails to move to the correct worksheet

the reference is moved correctly if you do the cut/paste by hand

for example:

if, sheet1 cell A1 contains "=B1"

and you cut sheet1 cell B1 and paste it to sheet2 cell C3

then sheet1 cell A1 will contain "=sheet2!C3"