条件付き書式グループ「非空白を罫線」の解説

概要

　書籍「Excel VBA開発を超効率化するプログラミングテクニック」の特典であるIkiKaiso2.xlamのリボン登録マクロの解説のための特設ページです。

リボン登録マクロ全体解説ページ⇒https://www.softex-celware.com/post/ikikaiso2

　本ページではIkiKaiso2.xlamの「条件付き書式」グループの「非空白を罫線」ボタンの使い方を解説します。

　表に値が入っていると自動的に罫線を設定するような条件付き書式を自動設定する

　実際に使っている様子の動画を下記でご確認ください。

　手順は次の通りです。

①：条件付き書式を設定したい表の表全体＋シート内の一番下までを選択する

②：「非表示を罫線」ボタンをクリック

③：①で選択したセル範囲が確認表示される。変更がある場合はこの段階で選択しなおす。

④：条件付き書式が自動設定される

　このような条件付き書式を設定することで「どの範囲まで値が入っているか」をすぐに見分けがつくので、ユーザーにとって使いやすくなるメリットがあります。

　ちなみに似たような設定として「テーブル化」もありますが、次のような理由でテーブルを利用したくない場合があります。

テーブルの範囲が大きく特に数式も含んでいると並び替えやフィルターなどの操作の遅延が大きくなる
テーブル範囲にVBAで二次元配列を一括出力を行おうとすると、出力する二次元配列の範囲に合わせてテーブル範囲を調整するような処理も必要になってくるので、VBAコードの構築が長くなる

　このような場面に対して上記のような非テーブル状態の表に条件付き書式で罫線を自動設定する処理が役に立ってきたりします。

　設定している条件付き書式は2つあります

条件①：B列(表の左端の列)が非空白なら左右に罫線を設定する

条件②：「B列が非空白」かつ「B列の1つ下のセルが空白」なら下に罫線を設定する

　このうち条件②に付いてですが、OFFSET関数を利用せずに次のような簡潔な数式でも実現します。

　しかしなぜ「OFFSET」関数を利用している理由ですが、これは表範囲で途中の行が消去や挿入が行われた場合に、OFFSET関数を使用しないと条件付き書式が破壊されてしまうためです。

　試しに行の消去を試してみます。条件付き書式は上記の「=AND($B3<>"",$B4="")」を使用しているとします。下記図のように12行目を丸ごと消去してみます。

　すると、表の下端の罫線が付かなくなりました。11行目(B11セル)の条件付き書式を確認してみると参照すべき1つ下のセルが「#REF!」となっており参照先を失っているエラーとなっていて、条件付き書式が破壊されているのが確認できます。

　このようにOFFSET関数を使用せずに一つ下のセルを参照するような数式だと行の消去によって、条件付き書式が破壊されてしまう恐れがあります。これは行の削除だけでなく行の挿入も影響を受けるので注意が必要です。

　上記のようなことも考慮して「非空白を罫線」の機能では設定する条件付き書式はOFFSET関数を利用しています。