Excel VBAを利用して集計表作成処理を自動化していますが、罫線を引いたりセルの色を変える処理のやり方をいつも忘れてしまい、個別に毎回調べるのも面倒なんです！
そんな貴方の為に、今回の記事では集計表の体裁を整える処理を自動化する際によく使うプログラム例を紹介するよ。
今回は、Excelで集計表の作成処理を自動化する際に、特定のセルの色を変えたり、罫線を引いたりといった「集計表の体裁を整える為に良く使うVBAの処理」を抜粋して紹介していきます。
是非ブラウザのブックマークに登録しておいてもらい、必要なときに呼び出してご活用ください。
シートをオブジェクト変数に格納するサンプルコード
今回の記事では、前提として、操作対象のシートを「Worksheetオブジェクト」化して、そのオブジェクトのメソッドやプロパティを操作します。
よって、まずはExcelのシートをWorksheetオブジェクト化するサンプルコードを紹介します。
'変数を宣言します。
Dim TargetSheet As Worksheet
'対象のシート名を指定してオブジェクト変数に代入します。
Set TargetSheet = Worksheets("対象のシート名")
'動作確認のためにMsgboxにシート名を表示します。
Msgbox TargetSheet.Name
'使い終わったオブジェクト変数を破棄します。
Set TargetSheet = Nothing
個人的には、いつもこの様にWorksheetオブジェクトを生成し、それを使用して各セルへの操作をしています。
シートの最下行位置と最右列位置を取得するサンプルコード
特定のシートに対して、使われてる最も大きい行位置と最も大きい列位置を取得します。
尚、当サンプルコードでは、特定のセルを選択した状態で、
キーボードの[End]キー＋[↓]キー同時押し
または
[End]キー＋[→]キー同時押し
をしたのと同じ挙動です。
具体的には、始点とした特定のセル配下の行、又は列にて空白のセルがあれば、その空白セルの手前のセルの位置を返します。
'変数を宣言します。
Dim TargetSheet As Worksheet
Dim MaxRow As Integer
Dim MaxCol As Integer
'対象のシート名を指定して変数に代入します。
Set TargetSheet = Worksheets("対象のシート名")
'セルA1を始点に最下行位置を取得します。
MaxRow = TargetSheet.Range("A1").End(xlDown).Row
'セルA1を始点に最右列位置を取得します。
MaxCol = TargetSheet.Range("A1").End(xlToRight).Column
Set TargetSheet = Nothing
対象列の範囲を指定して列の幅を自動調整するサンプルコード
特定のシートの列を範囲で指定して、その列に格納されている値に併せて列幅を自動調整します。
尚、当サンプルコードでは、特定の行や列を選択してダブルクリックすると、行を選択した場合はセルの高さ、列を選択した場合はセルの幅が自動的に調整されますが、その操作と同じ挙動です。
'変数を宣言します。
Dim TargetSheet As Worksheet
'対象のシート名を指定して変数に代入します。
Set TargetSheet = Worksheets("対象のシート名")
'A列からZ列の範囲のセルの横幅を自動調整します
TargetSheet.Range("A:Z").Columns.AutoFit
'1行目から100行目の範囲のセルの高さを自動調整します。
TargetSheet.Range("1:100").Rows.AutoFit
'範囲を変数で動的に指定したい場合は以下のようにします。
Dim MaxRow As Integer
Dim MaxCol As Integer
'セルA1を始点に最下行位置と最右列位置を取得します。
MaxRow = TargetSheet.Range("A1").End(xlDown).Row
MaxCol = TargetSheet.Range("A1").End(xlToRight).Column
'1行目から指定行位置までの範囲のセルの高さを自動調整します。
TargetSheet.Range(TargetSheet.Rows(1), TargetSheet.Rows(MaxRow)).Rows.AutoFit
'1列名から指定列位置までの範囲のセルの幅を自動調整します。
TargetSheet.Range(TargetSheet.Columns(1), TargetSheet.Columns(MaxCol)).Columns.AutoFit
Set TargetSheet = Nothing
特定の範囲のセルの背景色や文字色を変更するサンプルコード
特定の範囲のセルの背景色や文字色を変更します。
尚、VBAで色を指定するやり方はいくつかありますが、今回のサンプルコードでは、「Color」プロパティの値を直接指定する方法と、RGBで指定する方法の2種類を紹介します。
'変数を宣言します。
Dim TargetSheet As Worksheet
'対象のシート名を指定して変数に代入します。
Set TargetSheet = Worksheets("対象のシート名")
'A1からZ1までのセルの背景色を青色に変更します。
TargetSheet.Range("A1:Z1").Interior.Color = 16711680
'RGBで指定する場合は以下
TargetSheet.Range("A1:Z1").Interior.Color = RGB(0,0,255)
'A1からZ1までのセルの文字色を白色に変更します。
TargetSheet.Range("A1:Z1").Font.Color = 16777215
'RGBで指定する場合は以下
TargetSheet.Range("A1:Z1").Font.Color = RGB(255,255,255)
Set TargetSheet = Nothing
尚、「Color」プロパティに値を直接する場合の、値の確認方法としては、指定した色でセルの背景色などのいった手動で変更したうえで、その値をVBAで取得してみる方法があります。
Dim ColorNum As Long
'A1セルの文字色の値を取得します。
ColorNum = TargetSheet.Range("A1").Font.Color
Debug.Print ColorNum
特定のセルの範囲に対して罫線を引くサンプルコード
特定の範囲のセルに罫線を引きます。
罫線もいくつか種類がありますが、今回のサンプルではセル毎の格子状に罫線を引きます。
罫線を引くセルの範囲を、セル位置を固定で指定する場合と、動的に指定する場合の2種類を紹介します。
'変数を宣言します。
Dim TargetSheet As Worksheet
'対象のシート名を指定して変数に代入します。
Set TargetSheet = Worksheets("対象のシート名")
'対象のセル範囲を固定で指定して罫線を引きます。
'A1からZ100までのセルに罫線を引きます。
TargetSheet.Range("A1", "Z100").Borders.LineStyle = True
'罫線を引く対象のセル範囲を動的に変更する場合の例は以下
Dim MaxRow As Integer
Dim MaxCol As Integer
MaxRow = TargetSheet.Range("A1").End(xlDown).Row
MaxCol = TargetSheet.Range("A1").End(xlToRight).Column
TargetSheet.Range(TargetSheet.Cells(1, 1), TargetSheet.Cells(MaxRow, MaxCol)).Borders.LineStyle = True
Set TargetSheet = Nothing
特定の範囲のセルの書式(表示形式)を変更するサンプルコード
特定の範囲のセルの書式(表示形式)を変更します。
'変数を宣言します。
Dim TargetSheet As Worksheet
'対象のシート名を指定して変数に代入します。
Set TargetSheet = Worksheets("対象のシート名")
'対象のセル範囲を固定で指定して罫線を引きます。
'A2からZ10までのセルの書式(表示形式)を「通貨」にします。。
TargetSheet.Range("A1", "Z10").NumberFormatLocal = "\#,##0;\-#,##0"
'対象のセルの範囲を動的に指定して書式(表示形式)を変更する例は以下
Dim MaxRow As Integer
Dim MaxCol As Integer
MaxRow = TargetSheet.Range("A1").End(xlDown).Row
MaxCol = TargetSheet.Range("A1").End(xlToRight).Column
TargetSheet.Range(TargetSheet.Cells(2, 2), TargetSheet.Cells(MaxRow, MaxCol)).NumberFormatLocal = "\#,##0;\-#,##0"
Set TargetSheet = Nothing
尚、設定したい表示形式の書式(上記例であれば”\#,##0;\-#,##0″)を確認したい場合は、いったん手動でセルに表示形式を指定したうえで、VBAで対象のセルに設定した書式を取得する方法があります。
Dim FormatString As String
FormatString = TargetSheet.Range("B2").NumberFormatLocal
Debug.Print FormatString
ウィンドウ枠の固定を設定するサンプルコード
「ウィンドウ枠の固定」の有効化、無効化を設定します。
尚、「ウィンドウ枠の固定」は有効にした場合、そのシートを後述するクリア処理を実行してもその効果は残るのですが、毎回シートを消して再作成するといった処理の場合は、都度新しく作成したシート「ウィンドウ枠の固定」を有効化するといった使い方です。
'変数を宣言します。
Dim TargetSheet As Worksheet
'対象のシート名を指定して変数に代入します。
Set TargetSheet = Worksheets("対象のシート名")
'対象のシートを選択状態(Active)にします。
TargetSheet.Select
'「ウィンドウ枠の固定」が有効なら無効にします。
ActiveWindow.FreezePanes = False
'対象の行を選択状態にします。※1行目を固定する場合は2行目を選択
TargetSheet.Rows(2).Select
'「ウィンドウ枠の固定」を設定します。
ActiveWindow.FreezePanes = True
'特定のセル(例ではB2)を起点に「ウィンドウ枠の固定」を設定します。
ActiveWindow.FreezePanes = False
TargetSheet.Range("B2").Select
ActiveWindow.FreezePanes = True
Set TargetSheet = Nothing
シート全体のクリア、特定のセルのクリアをするサンプルコード
シート全体のクリア、又は特定のセルのクリアをします。
また、クリア内容は以下の種類から選択が可能です。
各メソッド名にはMicrosoftの公式ドキュメントへのリンクを貼っておきました。
| メソッド名 | クリア対象 |
|---|---|
| Clear | 全て |
| ClearComments | コメント |
| ClearContents | 値と数式 |
| ClearFormats | 書式 |
| ClearHyperlinks | ハイパーリンク |
| ClearNotes | コメント |
| ClearOutline | アウトライン |
因みに、上記のメソッド名の「ClearComments」と「ClearNotes」は一見同じ機能のように見えます。
Microsoftのリンク先のドキュメントを見ると、何故か「ClearComments」は戻り値の記述が無いことを除けば、ほぼ同じです。
まぁ、VBAでコメントを消すような処理はあまり使うことも無いかと思うので気にされなくて良いかと思います。
'変数を宣言します。
Dim TargetSheet As Worksheet
'対象のシート名を指定して変数に代入します。
Set TargetSheet = Worksheets("対象のシート名")
'シート全体のセルを初期化します。
TargetSheet.Cells.Clear
Set TargetSheet = Nothing
オートフィルタを有効化無効化するサンプルコード
オートフィルタを有効化又は無効化します。
'変数を宣言します。
Dim TargetSheet As Worksheet
'対象のシート名を指定して変数に代入します。
Set TargetSheet = Worksheets("対象のシート名")
'オートフィルタの状態を判定します。
If TargetSheet.AutoFilterMode = False Then
'オートフィルタを有効化します。
TargetSheet.Rows(1).AutoFilter
End If
Set TargetSheet = Nothing
よって、その処理の前に TargetSheet.AutoFilterMode でオートフィルターの状態に取得して無効な状態の場合だけ .AutoFilter を呼んでいます。
最後に
今回は、Excel VBAで集計表の作成処理を自動化する際に、集計表に罫線を引いたり、セルの色を変えるといった体裁を整える為に作成する処理をいくつか抜粋してサンプルコードを紹介しました。
コピペしてそのまま使えることを意識してサンプルコードを作成しました。
是非ご活用ください。
今回も読んでいただきありがとうございました。
