VBAの処理でRangeの行処理で1行分の処理が飛ばされてしまう

Question

EXCEL/VBAで質問です。Rangeを引いてきてForにて行を取得しているのですが
表示するだけの場合とカットペーストする場合で挙動が異なって困っています。
色々と確認してみましたがお手上げです。
どの様にすれば意図する様に全ての行で処理が行える様になるのかご教示のほど
お願いいたします。

'フォーム側の処理
Private Sub DeleteButton_Click()
    '選択している行番号を取得
    Call ExportText.DeleteDataMove(Selection.Rows)
End Sub

'処理モジュール
Function DeleteDataMove(DeleteRow As Range) As Boolean
On Error GoTo Error_Sub
    
    Dim TargetSheet As Worksheet
    Dim MoveTargetSheet As Worksheet
    Dim i As Long
    Dim MoveFirstRow As Long
    Dim j As Long
    Dim s As Range
    
    Set TargetSheet = ThisWorkbook.Sheets(1)
    Set MoveTargetSheet = ThisWorkbook.Sheets(2)
    
    MoveFirstRow = MoveTargetSheet.Cells(Rows.Count, 1).End(xlUp).Row
    i = MoveFirstRow + 1

   TargetSheet.Unprotect

    For Each s In DeleteRow.Rows
        Debug.Print "移動対象行：" & s.Row
    Next s

    For Each s In DeleteRow.Rows
        Debug.Print "カット対象：" & s.Row
        TargetSheet.Activate
        TargetSheet.Rows(s.Row).Select
        Selection.Cut
        MoveTargetSheet.Activate
        MoveTargetSheet.Rows(i).Select
        ActiveSheet.Paste
        i = i + 1
    Next s
    TargetSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    DeleteDataMove = True
    Exit Function

Error_Sub:
    MsgBox ("[ 例外 ] " & Err.Number & ":" & Err.Description)
    DeleteDataMove = False
End Function

上側のfor文の様に行表示のみであれば正常に動作するのですが、下側のfor文の様に
カットペーストを加えると先頭から2番目の行が処理されずに欠落してしまいます。
下記のログのような感じになってしまいます。やっていることはまったく同じです。

移動対象行：92
移動対象行：93   ←ここが処理されずに飛んでしまう。
移動対象行：94
移動対象行：95

カット対象：92
カット対象：94
カット対象：95

Answer 1

行を削除（またはカット）する場合、削除すると次の行は前へ移動します。
１行目を削除すると、２行目が１行目に移動して、２行目に３行目がきます。
そこで、２行目に処理すると、削除する前の３行目に対する処理になります。
削除する前の２行目には処理が行われません。

このように行削除が伴う処理は最後の行から前に移動しながら処理するのが鉄則です。
あるいは、前の行から処理しなければならないなら、下記のようなロジックにします。
対象行がなくなるまで１行目に対して処理をする。
あるいは、コピーを最後の行までしてから、対象行を削除する。

ちなみに、下記のページを、一度熟読してみましょう。

Office TANAKA - Excel VBA講座：セルの操作[セルのコピー]

やろうとしていることがもっとシンプルに効率的にできるかもしれません。

追記

上記はプログラミングでの一般論で回答しましたが、ExcelでのCut & Pasteの場合、この説明と実際の動作は少し異なりました。詳細は、jawaさんの回答を参照してください。

で、今回の質問のコードの目的は、選択範囲をSheets(2)へ移動させるというのが目的ですよね。

だとすると、ループで１行ずつコピーしなくても、Range ごとコピーすればすみます。
１行ずつ処理することでの問題点も発生せず、かつ、高速です。

    MoveFirstRow = MoveTargetSheet.Cells(Rows.Count, 1).End(xlUp).Row
    i = MoveFirstRow + 1

    TargetSheet.Unprotect

    For Each s In DeleteRow.Rows
        Debug.Print "移動対象行：" & s.Row
    Next s

    DeleteRow.Cut MoveTargetSheet.Rows(i) 'この１行だけ

    TargetSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    DeleteDataMove = True
    Exit Function

質問のコードは、マクロ記録したものをそのまま使っているだと思いますが、
「リンク先を熟読して」というのは、マクロ記録だけに頼らず、
そこからさらに追求して欲しいという含みをもたせたものでした。

Comments

[Otazoman]

コピーに書替えてみると事象は再発しなかったのでもしやと思っていたところ、
ご回答いただけていたようで、ありがとうございます。
ありがとうございます。本当の初歩的なところでした。

Answer 2

Rangeの境界行（先頭行または最終行）をカットすると、ペースト時にカットした行が対象範囲から除外され、範囲が狭まります。
※中間行をカット＆ペーストしても範囲は狭まりません。

今回の場合、ループ１周目でRangeの先頭行を処理するためこの流れで範囲が狭まり、元々の行番号が１つずつ繰り上がった状態になります。
これによりループ２周目でsにDeleteRow.Rows(2)を取得する際には、元々３行目だった行が取得されるということになるわけです。
ちなみにループ２周目以降のカットはRangeの中間行になるため範囲は狭まらないので、これ以降の行は期待した動作となっているというわけです。
じつは最終行を処理するときにも範囲が狭まっているのですが、最終行以降の行を参照することがないので問題となっていません。

以上により２行目だけが飛ばされたような動きになってしまうというわけです。

回避策としてはカット＆ペーストではなくコピー＆ペースト＆クリアにすればカット＆ペーストと同じような結果が得られると思います。

    For Each s In DeleteRow.Rows
        Debug.Print "カット対象：" & s.Row
        TargetSheet.Activate
        'TargetSheet.Rows(s.Row).Select
        'Selection.Cut
        'MoveTargetSheet.Activate
        'MoveTargetSheet.Rows(i).Select
        'ActiveSheet.Paste
        
        'コピー＆ペースト
        TargetSheet.Rows(s.Row).Copy MoveTargetSheet.Rows(i)
        'コピー元のクリア
        TargetSheet.Rows(s.Row).Clear
        
        i = i + 1
    Next s

---

以下は余談です。

基本的に行をカット＆ペーストすればコピー元の行はなくなるものと思っていましたが、どうやら貼り付け先が自シートか他シートかによってカットの動きは異なるようです。

①自シートに貼り付ける場合、カットした行は削除され以降の行が１行ずつ上に上がります。
②他シートに貼り付ける場合、カットしてもコピー元行は削除されず、行クリアだけされるようです。

今回は②のパターンですが、①の場合カットした行はなくなっていくため、先頭行から処理を進めるとカットするたびに次に処理する行の行番号がずれていってしまい、ループ処理が難しくなります。

こういうことを回避する為、行削除の絡む処理では処理対象の行番号を崩さないよう、あえて最下行から順に処理するという方法がよく使われます。

※最下行から処理する例（これだけだと貼り付け順も逆順になってしまいますが。）

    Dim iIdx As Integer
    
    For iIdx = DeleteRow.Rows.Count To 1 Step -1
        Set s = DeleteRow.Rows(iIdx)
        
        Debug.Print "カット対象：" & s.Row
        TargetSheet.Activate
        TargetSheet.Rows(s.Row).Cut MoveTargetSheet.Rows(i)
        TargetSheet.Rows(s.Row).Delete
        
        i = i + 1
    Next s

以上、参考になれば幸いです。

Comments

[jawa]

hatenaさんの回答で解決済みのようでしたが、+αの情報提供ができそうでしたので回答させていただきましたm(__)m

[hatena19]

動作確認なしにプログラミングの一般論で回答してしまいましたが、実際に実験してみないと駄目ですね。
詳細な検証ありがとうございます。勉強になりました。

[Otazoman]

ご回答ありがとうございます。結局は下記の処理で回避しています。
　・コピーしてから値をクリアする
　・ループで逆順で空白セルであれば行削除

EXCELの行削除はおくが深いです。
大変、勉強になりました。ありがとうございます。