【Excel VBA】10行くらいで表の罫線を自動的に綺麗に整えてみた

2018年6月25日2018年7月5日

仕事の報告レポートなどをExcelで作成している人って多いと思います。データを入力して表を作って体裁を整えて、いざ印刷してみると線が一部だけ欠けているとか種類が違うとかで焦ることもありがちです。ルーチン業務になっているとなおさら。

そこで、VBAを使ってサクッと表の罫線を整える方法をご紹介します。応用すればフォントなどの書式も綺麗にできますよ。

動作環境

• Microsoft Office 365 Soloを使用しています。
• WindowsでもMacでも動くと思いますが、Windows版の方が安定しているようです。

 

表を作るのって意外と面倒くさい

知り合いの方から毎月作ってるレポートが面倒だという話を聞きました。基本的には月に1度、まとめてデータを入力するだけなのですが、問題は前任者が作った表が微妙に扱いにくいこと。ただの表なのに、データ同士を区切る横線は点線で、見出し部分だけは塗りつぶしや太めの罫線が設定されており新しいデータを追加するたびに奇妙なルールに合わせて書式をいちいち変更しなくてはならないのだとか。

だったら、データだけ入れたら勝手に罫線引くようにしてみる？ということになりました。

 

表を用意します

ではサンプル用の表を用意します。実物はもっとゴチャゴチャしていましたが、わかりやすくシンプルに作ってみます。

2行目のB、C、D列を項目の見出しセルにして、適当に書式を設定します。見出しの下側だけ二重線で罫線を引いてみました。

次にデータの項目。3行目のB列に何か入っていたら、そこにはデータが存在しているというルールにします。罫線は自動で引いてもらうので何も設定していません。以下の例ではとりあえず3行目から5行目までデータが入っているということにします。

 

で、早速10行プログラム！

先にコードを載せます。続いて内容を解説していきますね。

よく見たら12行ありましたね。まぁ10行くらいなので多めにみてください。

このサンプルではkeisenという名前をつけました。

次の行でstart_rowに3を設定していますが、これは3行目からデータが入っているよ〜とプログラムに教えています。次のstart_colは2列目からデータが始まるよ、その次のend_colは4列目でデータが終わるよということを事前に設定しています。表の位置や項目の数に合わせてこの数値を変えることができます。

その次のi = start_rowは、これから実行されるプログラムのスタート地点をセットしています。i（プログラマがよく使うだけで、別にaでもbでも良いですよ）はデータの入っている行を指していて、まず最初はstart_rowに設定してある3行目から、1行づつ下に降りていきます。

ここからが本格的な動作に入ります。

Do while〜の行は繰り返し処理の始まりを表します。B列を3行目から見ていき、データが入っている間だけ、loopという命令までの処理を繰り返します。

繰り返している中身は、難しいこと書いてあるようですが実は簡単。罫線を引いているだけです。Rangeという命令は指定した2箇所のセルの間を範囲指定します。2箇所の指定はCellsという命令で行っており、最初の状態だとiは3なので、3行2列目から3行4列目までの範囲を選択しています。

その後ろにある.Borders.LineStyle = xlContinuousは、上下左右に実線を引くという命令で、次の行にある.Borders(xlEdgeTop).LineStyle = xlDotの方は選択範囲の上の部分だけ破線を指定しています。これてデータの区切りだけを破線に変更しています。

で繰り返し処理の最後にあるi = i + 1で、次の繰り返しでは、1つ下の行に対して罫線を引きますよ、と示しています。この時点で最初は3だったi の内容が4に、そして次は5へと1つづつ増えていきます。

データがなくなるところまで罫線を引き終えたら、最後の仕上げです。loopの次の行では、全てのセルの上側だえ破線を引いてしまったので、見出しのすぐ下だけ二重線にするため追加しました。もっと良い方法があるかもしれませんが、面倒なので強引に上書きしています。

 

実行結果

こんな感じです。罫線なんて気にせずにとりあえずガシガシデータを入力して、最後にVBAのプログラムを実行すれば勝手に体裁を整えてくれるようになりました。表の周囲は実線なのに、データの区切り部分はちゃんと点線になりました。

このプログラムを応用すれば、例えば以下を追加すれば、フォントや字のサイズを一括変更することもできます。コピペしたら書式が変わっちゃった！なんてことも防げるようになりますね。

 

おわりに

普段からメンドクセーと思っているルーチン作業の中には、意外と簡単に自動化できるものもあります。今ではExcelに限らずGoogle Spread Sheetなどでも簡単に作業の自動化が可能になりましたので、できるだけしょーもない作業は楽をしたいものですね。

Image by rawpixel.com