基準セルから表範囲セルを自動取得する

＜概要＞

　今回は基準セルから表範囲セルを自動取得する汎用プロシージャ「GetCellArea」の紹介です。

　シート上に下記図のような表がシート上にある時に、例えば「番号」と入力しているB2セルを基準として、この表の範囲を自動的に取得して(ヘッダーは除く)セル範囲(図ではB3:I22)として返すような関数となります。下記の表は例では20件が入力されていますが、こちらの件数は随時変化する前提となり、その変化量に応じて表の取得範囲は変わってきます。

　下記のようなシート内から情報を取得するという処理は、Excel VBAで頻繁に発生します。

＜実行例＞

　汎用プロシージャ「GetCellArea」の、従業員名簿を二次元配列として返す処理での使用例です。

　なお、基準セルのB2セルはセル範囲が移動しても問題ないように名前定義として「番号」と入れてあります。

　ちなみに上記のような「シート上の表を二次元配列として取得するコード」は下記記事でも紹介しています。頻繁に記述するコードなので実際の開発時は半自動的にコードを生成して開発の効率化を図っています。

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

　汎用プロシージャ「GetCellArea」は4つの引数（1つの通常引数、3つのOptional引数）を与えるようになっています。

　それぞれの引数の説明は次の通りです。[]で囲まれているものはOptional引数で省略可能です。

• 第1引数：StartCell ・・・基準セル

• 第2引数：[ColCount] ・・・セル範囲の列数(省略なら基準セルから自動で探索)

• 第3引数：[StartRow] ・・・セル範囲の範囲内での開始行番号(省略なら1で最初の行からの範囲、ヘッダー行など1行目の項目行を省きたい場合は2)

• 第4引数：[MaxBlankCount]・・・最終行判定用の空白セルの連続個数(指定個数の空白セルが連続したら最後の非空白セルが最終セル) (人為的に非表示行の存在があり得る場合は0以外にする)

　実行例のコードでは「GetCellArea(Cell, 8, 2)」として実行しており、設定した引数は次のような設定となります。

• 第1引数：基準セルは「番号」と名前定義が設定してあるB2セル

• 第2引数：列数は8と固定

• 第3引数：開始行番号は2としてヘッダー部分を除外する

• 第4引数：省略(任意的な非表示行は無しと判断)

　図でも説明すると次のような感じです。

　「オートフィルターあり」の場合

　下記図のようにオートフィルターが設定してあっても正確に表範囲を取得できます。こちらはGetCellAreaの子プロシージャGetEndRowにおいてオートフィルターの有無をしっかり検知して、分岐した処理を行っているためです。詳細は下記を参照してみてください。

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

　第2引数の列数の指定について

　上記のサンプルコードでは第2引数のColCountを表の列数と同じ8と指定していましたが、こちらを省略した場合は「列数を自動的に取得する」となりますが、下記の様に同じ結果が返ります。

　具体的に何をやっているかは下記図で説明しています。

　ここから注意事項として、名簿の表の右側に別の表（例：部署一覧）があるとします。

　すると結果は下記図のように取得セル範囲は「B3:K22」となり、右側に追加された表の分余分にセル範囲を取得しています。

　これは第2引数ColCountを省略したことで、自動取得した列数がK列までと計算された結果です。

　上記の様に1つのシートに複数の表を配置したりすることも多くあるので、第2引数ColCountの省略は気を付けてください。

　ほとんどの場合は表の列数は変わらないので、第2引数はその列数で指定(固定)しておいたほうが良いです。もし仕様変更で列が追加、消去されて列数が変更がある場合はこの第2引数の部分だけ変更する対応を取るようにしています。

　ヘッダーも含めたセル範囲を取得したい

　ほとんどレアケースでケースですがヘッダーも含めたセル範囲を取得したい場合は第3引数を1と指定してください。意味は「1行目から開始」となってヘッダーも含めたセル範囲が取得できます。

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

　これまたレアケースですが人為的は非表示行が存在する場合とします。

　すると次のような実行結果で取得したセル範囲は表示範囲の「B3:I16」で16行目までしか取得できませんでした。

　上記のオートフィルターでの非表示行には対応できたのですが、人為的な非表示行には対応できませんでした。

　この場合は第4引数の出番となります。

　具体的に下記の様に第4引数の「MaxBlankCount」を「1」と指定します。これは「1つでも連続して空白が続くセルなら最終行と判定」するようになり、正確に最終行が取得できるようになります。

　ほとんどの場合は不要ですが、用心深く実装する場合は第4引数も指定したほうが良いです。

＜関連コード＞

　今回の汎用プロシージャは、下記の別で紹介している汎用プロシージャを使用しています。

・GetEndCol→https://www.softex-celware.com/post/getendcol

・GetEndRow → https://www.softex-celware.com/post/getendrow

＜関連記事＞

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

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

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

＜コード＞