質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

2回答

7288閲覧

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

Otazoman

総合スコア44

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2017/05/09 07:56

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

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

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

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

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

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

追記

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

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

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

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) 'この1行だけ TargetSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True DeleteDataMove = True Exit Function

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

投稿2017/05/09 08:58

編集2017/05/09 13:21
hatena19

総合スコア33620

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

Otazoman

2017/05/09 09:08

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

0

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

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

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

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

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

以下は余談です。

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

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

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

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

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

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

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

投稿2017/05/09 10:53

jawa

総合スコア3013

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

jawa

2017/05/09 10:54

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

2017/05/09 12:28

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

2017/05/15 00:36

ご回答ありがとうございます。結局は下記の処理で回避しています。  ・コピーしてから値をクリアする  ・ループで逆順で空白セルであれば行削除 EXCELの行削除はおくが深いです。 大変、勉強になりました。ありがとうございます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問