基準セルから最終行番号を取得する

yuji fukami
10月18日
読了時間: 5分

＜概要＞

　今回は基準セルから最終行番号を取得する汎用プロシージャ(GetEndRow)の紹介です。

　別記事で紹介したGetCellAreaで使用している汎用プロシージャとなります。是非GetCellAreaの使い方も参考にしてみてください

GetCellArea → https://www.softex-celware.com/post/getcellarea

　最終行番号の取得の処理はExcel VBAでは頻出です。頻出でありながら多くの落とし穴が存在します。

　具体的な落とし穴として「オートフィルターによる非表示行の存在」「人為的な非表示行の存在」などです。

　それら落とし穴もしっかり考慮して、対策も打てるようにGetEndRowは構築しており、実際の開発の現場で実績のあるコードとなっています。

＜実行例＞

　汎用プロシージャ説明

　汎用プロシージャ「GetEndRow」の引数は次のようになっています。

第1引数：StartCell ・・・探索する基準の開始セル
第2引数：[MaxBlankCount]・・・最終行判定用の空白セルの連続個数
指定個数の空白セルが連続したら最後の非空白セルが最終セル
人為的に非表示行の存在があり得る場合は0以外にする(滅多にない)

　詳しい使い方は下記で説明していきます。

　実行例

　まず下記のような表がシート上に入力されているとします。

　ここでB2セルを基準に最終行番号を取得するとします。

　次のようなサンプルコードを用意します。

　なおB2セルはセルが移動しても大丈夫なように名前定義で「番号」と設定してあります。

　シート内で行、列が挿入、消去されてレイアウトが変わることによるセルが移動することは良くありますので、その影響を受けないための工夫です。

　実行結果は次のようになり、正しく最終行番号「22」が取得できています。

　以降で、冒頭で説明した落とし穴に対してどのような結果となるか、対策を打っているかを説明します。

　オートフィルターが設定してある場合

　下記図のように表にオートフィルターがかかっている場合です。

　この場合でも正確に最終行番号が取得できているのが確認できます。

　最終行番号取得の処理ではよくExcel VBAの教材では「Rangeオブジェクト.End」メソッドを利用する方法を解説されていますが、この落とし穴をめんどくさがって解説していないものが多くあります。

　このように「取得対象の表にオートフィルターがかかっている」場面は実務でExcel VBAを扱う場合はほぼ必ず現れる場面ですので注意が必要です。

　実際のGetEndRowのコードの中身で説明すると、処理の途中で対象のワークシートがオートフィルターがかかっているかどうかを判定して、オートフィルターがかかっている場合は愚直に空白セルの数を数えて最終行番号を取得するようになっています。

　人為的な非表示行が存在する場合

　レアケースですが、下記図のようにオートフィルターではなく人為的に非表示行が存在する場合の結果を見てみましょう。

　結果は表示されている範囲までの行番号の取得で正しく取得ができませんでした。

　この際は第2引数の「MaxBlankCount」の出番です。

　第2引数「MaxBlankCount」は「最終行判定用の空白セルの連続個数」で、指定した個数の空白セルが連続したら最後の非空白セルが最終セルと判定されるようになります。

　試しにMaxBlankCount = 1と与えてみた結果が次のようになり、正しく最終行番号が取得できました。

　こちらは最終行番号を与えることで先ほどのAutoFilterが設定してある場合と同じ分岐の処理で、「愚直に空白セルを取得する」ようにしているので、結果的に正確に最終行番号が取得できるようになります。

　もう一度言いますが、人為的な非表示行があるのはレアケースなので、そこまで考慮する必要はありませんが、信頼性の高いVBAを構築する場合はこのようなことも考えなければなりません。

　そもそも最終行取得方法に関して

　上記では最終行取得について次のように処理を分岐していました。

オートフィルターで非表示行あり → 空白セル数え上げ
人為的な非表示行あり → 空白セル数え上げ（開発者が判断）
上記以外で非表示行無し → Rangeオブジェト.Endメソッド使用

　この中で最終行取得の方法を2つ紹介しました

Rangeオブジェクト.Endメソッド
空白セル数え上げ

　実はこれ以外にも次のような方法があり、いろんな教材やブログで紹介されています。

Worksheetオブジェクト.UsedRangeプロパティ
Rangeオブジェト.CurrentRegionプロパティ
Rangeオブジェクト.Findメソッド
Rangeオブジェクト.SpecialCellsメソッド

　これらの他の方法がある中で、筆者が「空白セル数え上げ」を採用している理由として「最も確実に最終行を取得できる」ためです。

　「Rangeオブジェクト.Endメソッド」含めて「空白セル数え上げ」以外の方法はすべて何らかの弱点（落とし穴）が存在します（ここでは解説を割愛します）。

　もちろん「空白セル数え上げ」の弱点として「処理が遅い」というものがありますが、実務で利用する上ではたかが知れている処理速度の差ですので、実際の開発の現場では全く問題は発生せず困っておりません。　

＜関連記事＞

　今回紹介しているような汎用プロシージャを部品として一元管理する方法は別記事で紹介していますので、是非このノウハウも参考にしてみてください。

　コーディングを効率化する上でどんどん増やしていった汎用プロシージャ（部品）をいくら増やしても簡単に流用ができるような仕組みの構築が可能になります。

https://www.softex-celware.com/post/supervba003