【VBAで罫線を引く】最終行を自動判別するマクロの導入方法を紹介
罫線を引くVBAってどうやって作るの？
こんな疑問にお応えします。
この記事では、罫線を引くVBAの基礎的な考え方から一覧表の行や列が増減しても自動的に罫線の位置を調整できるVBAの作り方を詳しくご紹介しています。
VBA未経験の方でも理解しやすいように実際にVBAを作成する画面やコピペして使えるサンプルコードを使って解説しているので、
罫線を引く処理を自動化してエクセル業務に効率をアップさせたい！罫線を整備して常にキレイな一覧表を維持したい！と考える方は是非最後までチェックしてください。
この記事を参考にすれば、VBAについて深い理解がない方でも罫線を引く処理を自動化することが可能です。
罫線を引くVBAはこんな方におすすめ
- 一覧表を編集する業務が多い
- 常に罫線をキレイに保ちたい
- 罫線を引く作業を自動化し効率化を図りたい
エクセルを使って資料作成業務が多かったとき、「資料をプリントアウトしたあとに罫線の修正忘れに気づく」という失敗を何度も繰り返していました。
疲れ果てた人
罫線を引くVBAを使えば、ぐちゃぐちゃになった罫線を一瞬で整った一覧表に編集することが可能なので、
罫線の編集の手間や修正忘れのストレスがなくなります。
罫線を引く範囲の最終行を自動判別するマクロ
罫線を引く範囲の最終行を自動判別するマクロは、表の列や行が増減しても罫線を引く範囲を自動的に調整してくれるVBAのことです。
一覧表の編集作業では、行や列を挿入または削除を行うと罫線の表示が一覧表からズレてしまうことがありますよね。
そんなとき、罫線を引く範囲の最終行を自動判別するマクロを利用すれば、罫線の表示範囲を自動調整することが可能です。
下記の動画で、罫線を引く範囲の最終行を自動判別するマクロがどのような処理をするのかを確認することができます。
正解の人
一覧表の行数を頻繁に変更する方におすすめのマクロになっています。
ここからは、罫線を引く範囲の最終行を自動判別するマクロの使い方、導入方法を詳しくご紹介していきます。
罫線を引く範囲の最終行を自動判別するマクロの使い方
マクロの使い方といってもマクロが設定されたボタンを押下するだけ！というめちゃくちゃシンプルな方法なので、
VBA未経験の方でも感覚的に罫線を引く範囲の最終行を自動判別するマクロを利用することができます。
下記の画面のように、「罫線表示①」というボタンを作りマクロを登録
マクロを登録した「罫線表示①」ボタンを押下するだけ罫線を引くことができます。
マクロを使って罫線を引いた結果がこちら
情報を追加して最終行の場所が変化したとしても自動的に適切な範囲で罫線を引くことができます。
ガッツポーズの人
罫線を引く範囲の最終行を自動判別するマクロの導入方法
罫線を引く範囲の最終行を自動判別するマクロの導入方法を実際の操作画面を使って解説していきます。
手順に沿って操作すればVBAの知識がない方にでも導入可能なので是非挑戦してみてください。
1.VBAを起動
2.標準モジュールを作成
3.ModuleにサンプルVBAを記載
4.サンプルVBA
Sub テスト()
g = Cells(Rows.Count, 1).End(xlUp).Row
r = Cells(1, Columns.Count).End(xlToLeft).Column
Range(Cells(1, 1), Cells(g, r)).Borders.LineStyle = xlContinuous
Range(Cells(1, 1), Cells(g, r)).Borders.Weight = xlThick
Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideVertical).LineStyle = xlContinuous
Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideVertical).Weight = xlThin
Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideHorizontal).Weight = xlThin
End Sub
5.ボタンを作成
6.作成したボタンにマクロを登録
マクロ名のリストにはModuleに作成したサンプルVBAが自動的に表示されます。「テスト」を選択して「OK」を押すと作成したボタンにサンプルVBAが登録されます。
罫線を引く範囲の最終行を自動判別するマクロの導入はこれで完了です。
「これは使える！」
「自分でマクロを導入したい！」
と感じてくれた方はこの後の記事も是非チェックしてみてください。
罫線を引くVBAの基礎やVBA未経験者がマクロを導入する方法をご紹介しています。
罫線を引くVBAの基礎的な理解を深めると、応用編である「罫線を引く範囲の最終行を自動判別するマクロ」を簡単に導入することができます。
罫線を引くVBA導入のメリット
「VBAで罫線を引く」とは、どれだけ複雑な一覧表にも一瞬で罫線引くことができるということです。
例えば、
外枠を太めの二重線で内側を細めの実線にする
という操作も一瞬で処理できるということです。
ひらめく人
罫線を引くVBAを自由自在に扱えるようになれば、以下のような処理も自動化することができます。
- 外枠は太い線で引く
- 項目ごとに点線を引く
- タイトルは二重線で引く
- 罫線の色を変える
罫線の種類や太さを区別するのは意外と手間と時間がかかる作業ですが、VBAを使えば一瞬で処理が可能です。
罫線を引くVBAをすぐに使ってみたい！と感じてくれ方は下記のサイトをチェックしてみてください。
動画で紹介している罫線を引くVBAをダウンロードすることができます。
罫線を引くVBAの作り方
罫線を引くVBAは、(.Bodersプロパティ)を使用します。
基本構造
Range.Borders( 罫線の位置 ) .LineStyle = 罫線の種類
罫線を引く範囲を指定する方法としては、Range.Borders または Cells.Bordersどちらでも作成可能。
(.Bodersプロパティ)の後に「罫線の種類」や「罫線の太さ」を指定することでいろんな種類の罫線を引くことができます。
ひらめく人
罫線を引く位置の指定
罫線を引く範囲を指定したあとに罫線を引く位置を指定しなければなりません。
範囲の左辺の罫線を引くのか、右辺の罫線を引くのか、明確に指定するということです。
罫線を引く位置を指定するVBAついては下記の基本構造を参考にしてください。
基本構造
Range.Borders( 罫線の位置 ) .LineStyle = 罫線の種類
| 罫線の位置 | 罫線の位置 |
| xlEdgeTop | 上辺 |
| xlEdgeBottom | 下辺 |
| xlEdgeRight | 右辺 |
| xlEdgeLeft | 左辺 |
| xlInsideVertical | 内側の垂直線 |
| xlInsideHorizontal | 内側の水平線 |
| xlDiagonalUp | 右上がり線 |
| xlDiagonalDown | 右下がり線 |
基本構造の「罫線の位置」の部分に上記一覧表の値をいれることで、罫線を引く位置を指定することができます。
なお、「Range.Borders .LineStyle = 罫線の種類」のように罫線の位置を指定しない場合は上下左右に罫線が引かれます。
C3セル～E5セルの範囲で上辺の罫線を引くVBAを作ってみました。
Sub サンプル()
Range(Cells(3, 3), Cells(5, 5)).Borders(xlEdgeTop).LineStyle = xlContinuous
End Sub
Sub サンプル()
Range("C3:E5").Borders(xlEdgeTop).LineStyle = xlContinuous
End Sub
上記２つのサンプルコードは同じ意味です。
VBAを実行するとこんな感じ
ひらめく人
罫線の種類を指定する
罫線には実線や点線などの種類が豊富にあります。VBAを使って罫線を引く場合、この線種を指定しなければなりません。
線種を指定するVBAついては下記の基本構造を参考にしてください。
基本構造
Range.Borders( 罫線の位置 ) .LineStyle = 罫線の種類
| 罫線の種類 | 罫線の種類 |
| xlContinuous | 実線 |
| xlDash | 破線 |
| xlDashDot | 一点鎖線 |
| xlDashDotDot | 二点鎖線 |
| xlDot | 点線 |
| xlDouble | 二重線 |
| xlSlantDashDot | 斜め斜線 |
| xlLineStyleNone | なし |
基本構造の「罫線の種類」の部分に上記一覧表の値をいれることで、線種を指定することができます。
C3セル～E5セルの範囲で右辺に二重線の罫線を引くVBAを作ってみました。
Sub サンプル()
Range(Cells(3, 3), Cells(5, 5)).Borders(xlEdgeRight).LineStyle = xlDouble
End Sub
Sub サンプル()
Range("C3:E5").Borders(xlEdgeRight).LineStyle = xlDouble
End Sub
上記２つのサンプルコードは同じ意味です。
VBAを実行するとこんな感じ
罫線の太さを指定する
罫線には「極細」や「太い」といった線の太さを指定することができます。線の太さをしっかり区別することで見やすい表を作成することが可能になります。
線の太さを指定するVBAついては下記の基本構造を参考にしてください。
基本構造
Range.Borders( 罫線の位置 ) .Weight = 罫線の太さ
| xlHairline | 極細 |
| xlThin | 細い |
| xlMedium | 中 |
| xlThick | 太い |
「LineStyle」ではなく「Weight」になるので注意が必要です。
C3セル～E5セルの範囲で左辺に太い罫線を引くVBAを作ってみました。
Sub サンプル()
Range(Cells(3, 3), Cells(5, 5)).Borders(xlEdgeLeft).LineStyle = xlContinuous
Range(Cells(3, 3), Cells(5, 5)).Borders(xlEdgeLeft).Weight = xlThick
End Sub
Sub サンプル()
Range("C3:E5").Borders(xlEdgeLeft).LineStyle = xlContinuous
Range("C3:E5").Borders(xlEdgeLeft).Weight = xlThick
End Sub
上記２つのサンプルコードは同じ意味です。
VBAを実行するとこんな感じ
罫線の色を変える
罫線は「黒」以外にも「赤」や「青」といった色を指定することができます。色を指定しなかった場合、「黒」に統一されます。
罫線の色を指定するVBAついては下記の基本構造を参考にしてください。
基本構造
Range.Border( 罫線の位置 ).color = RGB( 0 , 0 , 0 )
もしくは
Range.Border( 罫線の位置 ).color = 色の定数
| 黒 | vbBlack |
| 白 | vbWhite |
| 赤 | vbRed |
| 青 | vbBlue |
| 緑 | vbGreen |
| 黄 | vbTellow |
ひらめく人
C3セル～E5セルの範囲で下辺に赤色の罫線を引くVBAを作ってみました。
Sub サンプル()
Range(Cells(3, 3), Cells(5, 5)).Borders(xlEdgeBottom).Color = RGB(255, 0, 0)
End Sub
Sub サンプル()
Range("C3:E5").Borders(xlEdgeBottom).Color = vbRed
End Sub
上記２つのサンプルコードは同じ意味です。
VBAを実行するとこんな感じ
罫線を引くVBAは何種類もの書き方が存在します。
上記で紹介している基本的なサンプルコードを比較して自分に合ったVBAの作り方を見つけてみてください。
応用編(罫線の表示位置を自動調整する)
罫線を引くVBAの基礎を紹介してきましたので、ここからは応用編として罫線の表示位置を自動調整するマクロの導入方法を紹介していきます。
具体的な内容がこちら
- 表の始まりをセルで指定し罫線を引く
- 表の始まりをテキストで検索し罫線を引く
この2つの処理をするVBAの作り方をご紹介していきます。
表の始まりをセルで指定して罫線を引く
仕事では、A1セルから一覧表を作り始めることはほとんどありませんよね。
一覧表の上に、「補足説明」や「表の題目」が記載されていることがほとんどではないでしょうか。
上記の表のように、
シートの途中から始まる一覧表の罫線を引くときは「表の始まりをセルで指定して罫線を引くVBA」を使う
ことがおすすめです。
サンプル事例として、「一覧表の始まりをB4セルと指定し、終わりは自動的に調整する」というVBAの導入方法を解説していきます。
1.VBAを起動
2.標準モジュールを作成
3.ModuleにサンプルVBAを記載
4.サンプルVBA
Sub サンプル2()
retu = Cells(4, Columns.Count).End(xlToLeft).Column
gyo = Cells(Rows.Count, 2).End(xlUp).Row
''罫線をリセット''
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlDiagonalDown).LineStyle = xlNone
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlDiagonalUp).LineStyle = xlNone
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeLeft).LineStyle = xlNone
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeTop).LineStyle = xlNone
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeBottom).LineStyle = xlNone
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeRight).LineStyle = xlNone
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlInsideVertical).LineStyle = xlNone
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlInsideHorizontal).LineStyle = xlNone
''表の罫線を引く''
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeTop).LineStyle = xlContinuous
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeTop).Weight = xlMedium
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeLeft).LineStyle = xlContinuous
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeLeft).Weight = xlMedium
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeRight).LineStyle = xlContinuous
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeRight).Weight = xlMedium
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeBottom).LineStyle = xlContinuous
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlEdgeBottom).Weight = xlMedium
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlInsideVertical).LineStyle = xlContinuous
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlInsideVertical).Weight = xlThin
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
Range(Cells(4, 2), Cells(gyo, retu)).Borders(xlInsideHorizontal).Weight = xlThin
End Sub
5.ボタンを作成
6.作成したボタンにマクロを登録
マクロ名のリストにはModuleに作成したサンプルVBAが自動的に表示されます。「サンプル2」を選択して「OK」を押すと作成したボタンにサンプルVBAが登録されます。
7.マクロの実行結果
B4セルから始まる一覧表に罫線を自動的に引くことができました。
表の始まりをテキストで検索して罫線を引くVBA
一覧表の上の行を挿入または削除した場合、始まりの場所が変わってしまい罫線がズレてしまうという問題が発生してしまいます。
悩んでる人
そんな、一覧表の始まりがどこから始まるかわからない！ときに対応できるマクロが、「表の始まりをテキストで検索して罫線を引くVBA」です。
サンプル事例として上図のような一覧に罫線を引いていきます。
「日付」というテキストが一覧表の始まりで、終わりは自動的に調整する！という内容のVBAを作成してみます。
1.VBAを起動
2.標準モジュールを作成
3.ModuleにサンプルVBAを記載
4.サンプルVBA
Sub サンプル3()
Dim dater, dateg As Long
Dim txt1 As String
''''''表のはじまりのテキストを入力''''''
txt1 = "日"
''''''検索したテキストが見つからない場合VBAを終了''''''
Dim Rng11 As String
Rng11 = txt1 & "見当たりません。"
Set Rng1 = Cells.Find(What:=txt1)
If Rng1 Is Nothing Then
MsgBox Rng11
Exit Sub
Else
End If
dater = Cells.Find(What:=txt1).Column
dateg = Cells.Find(What:=txt1).Row
retu = Cells(dateg, Columns.Count).End(xlToLeft).Column
gyo = Cells(Rows.Count, dater).End(xlUp).Row
''罫線をリセット''
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlDiagonalDown).LineStyle = xlNone
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlDiagonalUp).LineStyle = xlNone
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeLeft).LineStyle = xlNone
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeTop).LineStyle = xlNone
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeBottom).LineStyle = xlNone
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeRight).LineStyle = xlNone
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlInsideVertical).LineStyle = xlNone
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlInsideHorizontal).LineStyle = xlNone
''表の罫線を引く''
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeTop).LineStyle = xlContinuous
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeTop).Weight = xlMedium
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeLeft).LineStyle = xlContinuous
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeLeft).Weight = xlMedium
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeRight).LineStyle = xlContinuous
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeRight).Weight = xlMedium
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeBottom).LineStyle = xlContinuous
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlEdgeBottom).Weight = xlMedium
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlInsideVertical).LineStyle = xlContinuous
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlInsideVertical).Weight = xlThin
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
Range(Cells(dateg, dater), Cells(gyo, retu)).Borders(xlInsideHorizontal).Weight = xlThin
End Sub
5.ボタンを作成
6.作成したボタンにマクロを登録
マクロ名のリストにはModuleに作成したサンプルVBAが自動的に表示されます。「サンプル3」を選択して「OK」を押すと作成したボタンにサンプルVBAが登録されます。
7.マクロの実行結果
「日付」というテキストからはじまる一覧表に罫線を引くことができました。
一覧表の左上の項目が「日付」である限り常に整った罫線を保つことができます。
ガッツポーズの人
VBA初心者が効率化マクロを導入する方法
VBAなんて扱ったことないけどエクセル作業を自動化するマクロを導入したい！
このような問題を抱えながら日々のエクセル業務をこなしている方がおおくいらっしゃるのではないでしょうか。
こんな問題を解決する方法がこちら
- マクロの開発を外注に依頼する
- マクロが設定されたエクセルファイルをダウンロードする
- 参考書やネットでVBAスキルを身に付ける
VBAの勉強を始める前は上記の方法で効率化マクロを導入していました。
完成されたマクロを使ってエクセル業務を処理する
↓
作業効率が２倍以上アップ！！
↓
便利すぎて自分でもマクロを設定したいと考え始める
↓
参考書やネットの情報でVBAスキルを身に付ける
↓
マクロ開発を代行できる程のスキルを習得
VBAは
他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
という特徴があります。
毎日のルーティーン作業にうんざりしている、だれでもできる単純作業に時間を費やしている、という方はあなたに合った方法でVBAを導入しちゃいましょう。
マクロの開発を外注に依頼する
マクロの開発を外注に依頼する導入方法は
VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法
です。
「こんなエクセル作業を自動化したい」「作った資料を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」
こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。
ガッツポーズの人
デメリットは、費用がかかることだけ。
開発内容、依頼先によってかかる費用は大きく異なります。
私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。
疑問がある人
ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。
開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、
費用が安い！スピード納期！！
でやらせてもらっています。
ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。
ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。
マクロが設定されたエクセルファイルをダウンロードする
VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』ですぐにエクセル作業を自動化することができます。
たとえば、
A４用紙に写真を貼り付けてコメントを入力する作業を自動化したい！という場合、
『写真を貼り付けるマクロ』が設定されたエクセルファイルをダウンロードすれば問題解決です。
実際に、『写真を貼り付けるマクロ』をダウンロードして使っている様子がこちら
ガッツポーズの人
『マクロが設定されたエクセルファイルをダウンロード』する導入方法は、
VBA初心者でもマクロを導入できる、費用が安い、
というメリットがあります。
しかし
VBAに関する情報が多く公開されているとはいえ、ネット上で想定するマクロは見つけることが難しい
というデメリットもあります。
ガッツポーズの人
個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。
今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。
参考書やネットでVBAスキルを身に付ける
先程も紹介しましたが、
VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
プログラミングです。
言い換えると、
情報量が豊富なので自分で勉強できちゃう
ということです。
私のVBAスキルはほとんど独学で身に付けたもので、
考えた処理のほとんどをVBAで表現できるレベルにまで到達した！
と思っています。
私の作ったマクロをプロのプログラマーが見れば、
お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！
と感じるはずです。
でも、
私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？
独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、
業務内容に合ったマクロを開発できるようになれます。
独学でVBAスキルを身に付けるコツは、
エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる
です。
とは言っても、全くの知識0の方は何をすればいいかわからないですよね。
VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。
VBAの知識0の方でもすいすい読み進められる内容になっています。
やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。
実際に私はこの参考書を読んでからVBAの勉強を始めました。
ひらめく人
VBAの魅力を発見した後は、
ひたすら自動化したい処理を実現するコードを書くだけ
です。
【VBA 写真を貼る】、【VBA PDF出力】、のようにネット検索すれば知りたいコードをすぐにゲットできます。
実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると
自然といろんなマクロが作れるようになっているはずです。
VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。