ExcelVBAでよく使われる繰り返し処理において、常にデータが入っている列が空白になったらループ処理を終わるように「DO WHILE」や「DO UNTIL」を使われていると思います。
他の人が作ったデータを処理する場合に、空白行があり、処理が途切れて困っていませんか？
今回はデータに空白行があっても最後のデータまで処理を行う方法を説明します。
解決方法
ループの中で空白行だったら空白カウンタを加算して、空白じゃない場合には空白カウンタをリセットします。
最終行以降もループは繰り返されますが、設定した空白カウント以上になったらループが終了します。
実例
下の図は、Ｂ列に固有の文字列があり、C列に処理を行うものとします。
シート名は「一覧表」になっており、データにはところどころ空白行を挿入してあります。
「月ごと」や「週ごと」の予定表等の予定表がこのような形式になっていることが多いですね。
空白対策コード
このコードは実例の処理を行うコードです。
Sub ボタン1_Click()
'空白があっても最終行まで処理する
Dim KCOUNT As Integer
Dim IROW As Long
KCOUNT = 0
IROW = 1 '処理開始行
Do Until KCOUNT >= 10 '空白カウンタが10以上になったらループ処理を抜ける
If Worksheets("一覧表").Cells(IROW, 2).Value = "" Then
KCOUNT = KCOUNT + 1 '空白なのでカウンタ加算
Else
KCOUNT = 0 '空白じゃない場合はカウンタをリセット
'ここに空白じゃない場合の処理を記述
'今回の参考例はC列の文字列に「B」が含まれていたら赤色塗りつぶしする
If InStr(Worksheets("一覧表").Cells(IROW, 3).Value, "B") > 0 Then
Worksheets("一覧表").Cells(IROW, 3).Interior.ColorIndex = 3
End If
'ここまで
End If
IROW = IROW + 1
Loop
MsgBox "処理完了"
End Subコピーして使用するためのコードを用意しましたので、コピーして部分的に修正して使用してください。
Dim KCOUNT As Integer
Dim IROW As Long
KCOUNT = 0
IROW = 1 '処理開始行
Do Until KCOUNT >= 10 '空白カウンタが10以上になったらループ処理を抜ける
If Worksheets("一覧表").Cells(IROW, 1).Value = "" Then
KCOUNT = KCOUNT + 1 '空白なのでカウンタ加算
Else
KCOUNT = 0 '空白じゃない場合はカウンタをリセット
'ここに空白じゃない場合の処理を記述
End If
IROW = IROW + 1
Loop実行結果画像
下の画像は実際に上記実例用コードで実行処理した結果です。
B列に文字列があり、C列の文字列に「B」が含まれていたら赤色塗りつぶしを実施しています。
最終の19行目まできちんと処理ができています。
DO – LOOPの検証時に気を付けること
マクロを実行の検証する前に必ずファイルの保存をしてください。
理由は、無限ループに入ってしまうとエクセルを強制終了しないといけなくなる場合があります。
強制終了した場合には、せっかく作ったプログラムコードが消失してしまうことがあります。
私は何度もプログラムが消失しました。
最近はエクセルの自動保存機能が実装されONにしておけばプログラムの消失リスクは少なくなっています。
無限ループが発生するケースには、下記のようなものがあります。
- カウントアップする変数名を間違える
- カウントアップ自体を忘れる
- カウントアップ変数とDO-LOOPの条件が無関係になっている
絶対に無限ループを回避したい方は、LOOPの前に「DoEvents]をいれておけば、ブレークなどの実行処理中断操作で処理を止めることができます。
そのかわりに、若干処理が遅くなります。
最終行まで実行する別の方法
せっかくなので、最終行まで実行する別の方法も説明しておきます。
下記コードを実行するとB列の最終行をlastrowという変数に取得することができます。
Range("B1048576").select
Selection.End(xlUp).Select
lastrow=Selection.Rowエクセルのショートカット「ctrl+↑」を実行すると上方向に値が入っているセルを検索することができます。その機能と同じ処理を２行目で行っています。
１行目はB列の最終行（エクセル2021の扱える最大の行）を選択しています。
lastrowに取得できた最終行までfor文やdo文で繰り返し処理をすれば、上で説明した方法と同様に処理することができます。
最後に
手段や方法は多数ありますので、自分の求める結果が得られるようにプログラミングしてみてください。
今後も、コピーしてちょっと修正すればすぐ使えるコードを紹介していきます。
ほかにも「python」や「GAS」などのコードも公開していますのでそちらの記事も読んでみてください。
コメント