空白セルまでループ
業務用のVBAでよく使うのが「空白セルまでループ」です。このサイトでもいろんなページで書いてます。
ここでは「空白セルまでループ」を中心に書いていますので、「空白セルかどうか」自体の判定方法についての詳細は「VBAで空白セルを判定する方法」をご参照ください。
コードでは以下が基本形になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Sub VoidCellLoop1() Dim i '// ループカウンタ Dim s '// セル値 '// A1セルをアクティブ Range("A1").Select '// ループカウンタを初期化 i = 0 '// 空セルまでループ Do '// セル値を取得 s = ActiveCell.Offset(i, 0).Value '// セル値が未設定の場合 If s = "" Then '// ループを抜ける Exit Do End If '// ループカウンタを加算 i = i + 1 Loop End Sub |
上のコードはアクティブセルを基準としてループでOffsetを使ってセル座標を１行ずつ下にずらしています。事前にアクティブセルをA1に設定しています。
行ではなく列を右にずらしたい場合は「Offset(0, i)」と引数の行と列を入れ替えるだけです。
セル値が未設定かどうかを「If s = “” Then」と書いていますが、これは「If IsEmpty(s) = True Then」と書いても結果は同じになります。
セル範囲を空白セルまでループ
上のコードはアクティブセルを指定して、そこから行を下に向かって空セルまでループしていますが、例えばA1セルからA100セル範囲の中のようにセル範囲の中で空セルまでループしたい場合があります。
その場合は以下のようなループ構文になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Sub VoidCellLoop2() Dim s '// セル値 Dim r As Range '// 対象セルのRangeオブジェクト '// 空セルまでループ For Each r In Range("A1:A100") '// セル値を取得 s = r.Value '// セル値が未設定の場合 If s = "" Then '// ループを抜ける Exit For End If Next End Sub |
For EachでA1からA100のセル範囲をループし、ループごとに対象の１セルをRangeオブジェクトで取得します。
取得したRangeオブジェクトのセル値をValueプロパティで取得し、あとは最初のコードと同じでセル値が未設定かどうかの判定を行います。
空白セルがある程度連続しているとループ終了する方法
最初に紹介したコードですが、空白セルまでループするということは、値が無ければ処理終了、ということになります。
ここで困るのが「1行ずつ空行があるフォーマットを使っている」場合とかです。こういう場合は空白セルがあると途端に止まってしまいます。しかしそれでは困ります。
そこで、空白セルがある程度連続していたら処理を終了するようにします。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 |
Sub VoidCellLoop3() Dim i '// ループカウンタ Dim s '// セル値 Dim iVoid '// 空白セルカウンタ '// A1セルをアクティブ Range("A1").Select '// ループカウンタを初期化 i = 0 '// 空白セルカウンタを初期化 iVoid = 0 '// 空セルまでループ Do '// セル値を取得 s = ActiveCell.Offset(i, 0).Value '// セル値が未設定の場合 If s = "" Then '// 空白セルが連続10回を超えた場合 If iVoid > 10 Then '// ループを抜ける Exit Do '// 空白セルが連続10回を超えていない場合 Else '// 空白セルカウンタを加算 iVoid = iVoid + 1 End If '// セル値が設定されている場合 Else '// 空白セルカウンタを初期化 iVoid = 0 End If '// ループカウンタを加算 i = i + 1 Loop End Sub |
１つ目のサンプルがベースになっていますが、空白セルが連続で何回繰り返したのかを数えるための変数を追加しています。
この空白セルカウンタ変数が10回を超えていたら、それ以降は空白セルしかないとみなしてループを抜けるようにしています。セルに値が入っていれば空白セルカウンタを0に初期化しています。
このようにすれば１行ずつ空白セルがあるようなフォーマットの場合でも対応が可能になります。