ExcelVBAを用いて表の書式を一括で設定する

はじめに

素データを表の一覧として見れるように加工するためのコードです。
簡単な事前準備をする必要がありますが、手動で設定するより速く正確に設定できます。

下記でお困りの方におススメ↓↓↓

・表の行または列が膨大にある
・表の行と列が可変になる
・書式設定用の共通関数が欲しい
・一覧表示の自動化の一部分として書式設定を含めたい

書式設定イメージ

下記の添付画像のように、事前に用意している書式を表示されたデータに一括設定します。

事前準備

表のタイトル行より上の行に、コピー用の書式設定行を用意してください。

サンプルコード

呼出元

Public Sub 呼び出し元プロシージャ()
    
    '()の中を設定してください
    Call 一覧書式設定(シートオブジェクト名, _               'シートオブジェクト名を指定（sheet1など）
                     書式設定済みの行番号, _               'コピー用の書式設定行の行番号を指定（数値）
                     データの表示開始行, _                 'データが表示されている最初の行番号を指定（数値）
                     データの最終行を取得するための列, _    '一覧の最終行がわかる列番号を指定（数値）
                     書式設定を開始する列, _               '書式設定を開始する列番号を指定（数値）
                     書式設定を終了する列)                 '書式設定を終了する列番号を指定（数値）

End Sub

書式設定

Public Sub 一覧書式設定(ByVal pwks_シート As Worksheet, _
                       ByVal plng_書式設定済行 As Long, _
                       ByVal plng_表示開始行 As Long, _
                       ByVal plng_最終行取得用列 As Long, _
                       ByVal plng_書式設定開始列 As Long, _
                       ByVal plng_書式設定終了列 As Long)
    
    Dim lng_最終行  As Long
    
    With pwks_シート
    
        '表の最終行を取得
        lng_最終行 = .Cells(Rows.Count, plng_最終行取得用列).End(xlUp).Row
        
        '一覧が表示されていないなら中断
        If lng_最終行 < plng_表示開始行 Then Exit Sub
        
        '書式設定済セル範囲をコピー
        .Range(.Cells(plng_書式設定済行, plng_書式設定開始列), .Cells(plng_書式設定済行, plng_書式設定終了列)).Copy
        
        '書式を表に貼り付け
        .Range(.Cells(plng_表示開始行, plng_書式設定開始列), .Cells(lng_最終行, plng_書式設定終了列)).PasteSpecial Paste:=xlPasteFormats

        'コピーした内容をクリップボードからクリア
        Application.CutCopyMode = False
        
    End With

End Sub

サンプルコード（書式設定）の説明

VBAがあまりわからない人向けに記載します。
下記の画像を元に説明します。

'表の最終行を取得
lng_最終行 = .Cells(Rows.Count, plng_最終行取得用列).End(xlUp).Row

選択した列（最終行取得用列）の最終行から上に向かって、１番最初にセルに値が入っている行番号を取得します。
例えば、添付画像のC列（3列）を最終行取得用列に設定すると「34」を取得します。

'書式設定済セル範囲をコピー
.Range(.Cells(plng_書式設定済行, plng_書式設定開始列), .Cells(plng_書式設定済行, plng_書式設定終了列)).Copy

'書式を表に貼り付け
.Range(.Cells(plng_表示開始行, plng_書式設定開始列), .Cells(lng_最終行, plng_書式設定終了列)).PasteSpecial Paste:=xlPasteFormats

書式設定済のセル範囲のデータをコピーします。
その後、指定した範囲に書式をペーストします。
画像のC5:G34を一括で書式を設定するイメージです。

おわりに

書式設定用の行を設けることで、VBA側の改修頻度が減るかと思います。
私はDBからデータを取得し、Excelに取得したデータを表示する際にかなり重宝しました。
皆様のお役に立てればと思います。