Excel VBA 逆引き集 | 実務テンプレ完全版（超再利用部品） – レイアウト修正テンプレ

Excel VBA
スポンサーリンク

ねらい：もらったExcelを「一発で“見れる形”に直す」レイアウト修正テンプレ

他部署や取引先からもらった Excel、こんな状態で届きませんか。

  • 列幅バラバラ・行高バラバラ
  • 罫線が中途半端／二重線／色がバラバラ
  • フォントが混在・サイズも段々畑
  • ヘッダーだけ太字・中央揃えにしたい
  • 印刷すると右端が切れる・ページが変なところで分割される

そのたびに「列幅調整→罫線書き直し→フォント揃え→印刷設定…」を手作業でやっていると、地味に時間を奪われます。

レイアウト修正テンプレのゴールは、

「貼り付けた生データに対して、ボタン1つで“見れる表・印刷できる表”に整える」

ための VBA 部品をセットで用意しておくことです。
用途ごとに部品を組み合わせて使えば、どんな表でも「だいたい同じ見た目」に揃えられるようになります。

共通基盤：範囲取得・安全な CurrentRegion・フォント／罫線の一括適用

基本ユーティリティ（全テンプレ共通の土台）

' ModLayout_Base.bas
Option Explicit

Public Function GetUsedBlock(ws As Worksheet, Optional ByVal topLeft As String = "A1") As Range
    ' A1 からの CurrentRegion を基本範囲とする
    ' 貼り付け位置を変えたい場合は topLeft を変える
    With ws.Range(topLeft)
        If .CurrentRegion.Rows.Count = 1 And .CurrentRegion.Columns.Count = 1 And IsEmpty(.Value) Then
            Set GetUsedBlock = Nothing
        Else
            Set GetUsedBlock = .CurrentRegion
        End If
    End With
End Function

Public Sub ApplyFontAndAlign(ByVal rng As Range, _
                             Optional ByVal fontName As String = "Meiryo UI", _
                             Optional ByVal fontSize As Double = 9)
    With rng
        .Font.Name = fontName
        .Font.Size = fontSize
        .VerticalAlignment = xlVAlignCenter
        .WrapText = True
    End With
End Sub

Public Sub ApplyBorder(ByVal rng As Range)
    With rng.Borders
        .LineStyle = xlContinuous
        .Weight = xlThin
        .Color = RGB(200, 200, 200)
    End With
End Sub

Public Sub AutoFitAll(ByVal rng As Range)
    rng.EntireColumn.AutoFit
    rng.EntireRow.AutoFit
End Sub
VB

ここがレイアウト修正の“核”です。

  • GetUsedBlock：A1 基準の CurrentRegion で「表の範囲」を拾う基本。
  • ApplyFontAndAlign：フォント名・サイズ・縦位置・折り返しを一括で整える。
  • ApplyBorder：標準的な罫線（薄いグレー）を一気に引く。
  • AutoFitAll：行と列の自動調整。

この4つを覚えておくだけで、「とにかくぐちゃぐちゃを整える」ことはほぼできます。

ヘッダ行・データ行の見た目テンプレ：1行目を“ヘッダっぽく”整える

見出し行（ヘッダ）の強調テンプレ

' ModLayout_Header.bas
Option Explicit

Public Sub FormatHeaderRow(ByVal ws As Worksheet, Optional ByVal headerRow As Long = 1, _
                           Optional ByVal fontName As String = "Meiryo UI")
    Dim blk As Range
    Set blk = GetUsedBlock(ws, "A1")
    If blk Is Nothing Then Exit Sub
    
    Dim hdr As Range
    Set hdr = blk.Rows(headerRow)
    
    With hdr
        .Font.Name = fontName
        .Font.Size = 9
        .Font.Bold = True
        .Interior.Color = RGB(230, 230, 250) ' 薄い色
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlVAlignCenter
        .WrapText = False
    End With
    
    ' 下のデータ行は上書きされないようにフォントだけ薄く揃える例
    With blk.Offset(1, 0).Resize(blk.Rows.Count - 1)
        .Font.Name = fontName
        .Font.Size = 9
        .Font.Bold = False
    End With
End Sub
VB

ここでの重要ポイントを深掘りします。

  • ヘッダ行（デフォルト1行目）は見た目が「太字＋背景色＋中央寄せ」であると、人は一瞬で“これは見出しだ”と認識できます。
  • データ行はあえて太字にしないことで、ヘッダとのコントラストが出ます。
  • WrapText（折り返し）はヘッダでは OFF にしておき、列幅を広げて見やすくします。

この Sub は「ヘッダ行を一段“テーブルっぽく”するだけ」の役割に絞ってあります。
他のテンプレと組み合わせて使えるように、役割を分けるのがコツです。

一括レイアウト修正：列幅／罫線／フォント／数値書式／グリッド線非表示

「もらった表」を一気に綺麗にする標準テンプレ

' ModLayout_Standard.bas
Option Explicit

Public Sub FixLayout_Standard(Optional ByVal sheetName As String = "")
    Dim ws As Worksheet
    If sheetName = "" Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(sheetName)
    End If
    
    Dim blk As Range
    Set blk = GetUsedBlock(ws, "A1")
    If blk Is Nothing Then
        MsgBox "整形対象のデータが見つかりません。", vbInformation
        Exit Sub
    End If
    
    ' 1) 全体のフォント・縦位置・折り返し
    Call ApplyFontAndAlign(blk)
    
    ' 2) ヘッダ行の強調（1行目前提）
    Call FormatHeaderRow(ws, 1)
    
    ' 3) 罫線を引き直す
    Call ApplyBorder(blk)
    
    ' 4) 列幅・行高の自動調整
    Call AutoFitAll(blk)
    
    ' 5) 数値列の書式（簡易：数値っぽい列に #,##0 をかける）
    Dim c As Long, r As Long, isNumericCol As Boolean
    For c = 1 To blk.Columns.Count
        isNumericCol = True
        For r = 2 To blk.Rows.Count ' データ行だけざっとチェック
            If Not IsEmpty(blk.Cells(r, c).Value) Then
                If Not IsNumeric(blk.Cells(r, c).Value) Then
                    isNumericCol = False
                    Exit For
                End If
            End If
        Next
        If isNumericCol Then
            blk.Columns(c).NumberFormatLocal = "#,##0"
        End If
    Next
    
    ' 6) グリッド線を消して「帳票っぽい」見た目に
    ws.Activate
    ActiveWindow.DisplayGridlines = False
    
    MsgBox "レイアウトの標準整形が完了しました。", vbInformation
End Sub
VB

このテンプレひとつで、ほとんどの“雑に送られてきた表”はだいぶマシになります。

重要なポイントを整理すると、

  • フォントと縦位置を揃えるだけで、ガタガタ感が減る。
  • 罫線を一度全消し→一括で引き直すことで、「太線／細線／色」バラバラ問題をリセットできる。
  • 数値列検出は簡易ですが「全部数値っぽい列だけに書式をかける」ので、文字コード列などへの誤適用を減らせます。
  • グリッド線を消すと、帳票やレポートのような“紙のドキュメント”感が出ます。

「とにかく見づらい表を、数秒で“最低限見れる表”に直す」ための基本技として、1本覚えておくと重宝します。

印刷レイアウト修正：1ページに収める・余白・ヘッダ行の繰り返し

印刷設定を一括で“まともにする”テンプレ

' ModLayout_Print.bas
Option Explicit

Public Sub FixPrintLayout(Optional ByVal sheetName As String = "")
    Dim ws As Worksheet
    If sheetName = "" Then
        Set ws = ActiveSheet
    Else
        Set ws = Worksheets(sheetName)
    End If
    
    Dim blk As Range
    Set blk = GetUsedBlock(ws, "A1")
    If blk Is Nothing Then Exit Sub
    
    With ws.PageSetup
        .Orientation = xlPortrait            ' 縦向き（横向きにしたければ xlLandscape）
        .Zoom = False
        .FitToPagesWide = 1                  ' 横1ページに収める
        .FitToPagesTall = False              ' 縦は自動（複数ページ可）
        
        .LeftMargin = Application.CentimetersToPoints(1.0)
        .RightMargin = Application.CentimetersToPoints(1.0)
        .TopMargin = Application.CentimetersToPoints(1.5)
        .BottomMargin = Application.CentimetersToPoints(1.5)
        .HeaderMargin = Application.CentimetersToPoints(0.8)
        .FooterMargin = Application.CentimetersToPoints(0.8)
        
        .PrintTitleRows = "$1:$1"            ' 1行目ヘッダを各ページに繰り返し表示
        .PrintArea = blk.Address             ' 表だけ印刷範囲にする
        .CenterHorizontally = True           ' 中央寄せ（左右）
    End With
    
    MsgBox "印刷レイアウトの整形が完了しました。印刷プレビューでご確認ください。", vbInformation
End Sub
VB

ここでの深掘りポイント。

  • Zoom を OFF にして FitToPagesWide=1 にすることで、「右端が切れる」「ページが変なところで折れる」問題がかなり減ります。
  • PrintTitleRows を指定すると、2ページ目以降にもヘッダ行が印刷されるので、紙で見ても迷子になりにくくなります。
  • PrintArea に CurrentRegion を当てることで、「謎の余白や離れたセル」が印刷される事故を防げます。

標準整形テンプレ（FixLayout_Standard）とセットで使うと、
「見た目整える→印刷レイアウト整える→PDF出力」の流れを一気に作れます。

列の入れ替え・非表示・並び順の統一：レイアウト定義表を使う

「この順で列を並べたい」を表にして、自動で並び替える

破壊力が大きいのが「列並びのバラバラを揃えるテンプレ」です。
これは少しだけレベルを上げますが、慣れると“超再利用部品”になります。

LayoutDef シートを1枚作り、こういう表を持ちます。

A列：FromHeader（元のヘッダ名）
B列：ToHeader（新しいヘッダ名。空なら同じ）
C列：OrderNo（並べたい順番。1,2,3,…）
D列：Visible（表示するか。Y/N）

例：

FromHeaderToHeaderOrderNoVisible
顧客名顧客名1Y
顧客ID顧客コード2Y
郵便番号郵便番号3Y
電話番号TEL4Y
内部メモ99N

この定義に基づいて、データシートの列を並べ替えるテンプレを作ります。

' ModLayout_ColumnOrder.bas
Option Explicit

Public Sub ReorderColumnsByLayout(ByVal dataSheet As String, ByVal layoutSheet As String)
    Dim wsData As Worksheet, wsLayout As Worksheet
    Set wsData = Worksheets(dataSheet)
    Set wsLayout = Worksheets(layoutSheet)
    
    Dim data As Variant, layout As Variant
    data = wsData.Range("A1").CurrentRegion.Value
    layout = wsLayout.Range("A1").CurrentRegion.Value
    
    Dim idxFrom As Object
    Set idxFrom = CreateObject("Scripting.Dictionary")
    idxFrom.CompareMode = 1
    
    Dim c As Long
    For c = 1 To UBound(data, 2)
        idxFrom(LCase$(Trim$(CStr(data(1, c))))) = c
    Next
    
    Dim orderList As Object
    Set orderList = CreateObject("Scripting.Dictionary")
    Dim l As Long
    For l = 2 To UBound(layout, 1)
        Dim fromName As String
        fromName = LCase$(Trim$(CStr(layout(l, 1))))
        If idxFrom.Exists(fromName) Then
            Dim ord As Long
            ord = CLng(layout(l, 3))
            If layout(l, 4) = "Y" Or layout(l, 4) = "y" Then
                orderList(ord) = fromName
            End If
        End If
    Next
    
    Dim keys() As Variant
    If orderList.Count = 0 Then Exit Sub
    ReDim keys(1 To orderList.Count)
    
    Dim i As Long, k As Variant
    i = 1
    For Each k In orderList.Keys
        keys(i) = k
        i = i + 1
    Next
    
    Dim j As Long, tmp
    For i = 1 To UBound(keys) - 1
        For j = i + 1 To UBound(keys)
            If CLng(keys(i)) > CLng(keys(j)) Then
                tmp = keys(i): keys(i) = keys(j): keys(j) = tmp
            End If
        Next
    Next
    
    Dim newCols As Long
    newCols = orderList.Count
    
    Dim out() As Variant
    ReDim out(1 To UBound(data, 1), 1 To newCols)
    
    For i = 1 To newCols
        Dim keyOrd As Long
        keyOrd = CLng(keys(i))
        Dim fname As String
        fname = orderList(keyOrd)
        Dim colSrc As Long
        colSrc = idxFrom(fname)
        
        Dim toHeader As String
        toHeader = ""
        For l = 2 To UBound(layout, 1)
            If LCase$(Trim$(CStr(layout(l, 1)))) = fname Then
                toHeader = CStr(layout(l, 2))
                Exit For
            End If
        Next
        If toHeader = "" Then toHeader = CStr(data(1, colSrc))
        
        out(1, i) = toHeader
        
        Dim r As Long
        For r = 2 To UBound(data, 1)
            out(r, i) = data(r, colSrc)
        Next
    Next
    
    wsData.Cells.Clear
    wsData.Range("A1").Resize(UBound(out, 1), UBound(out, 2)).Value = out
    FormatBlock wsData, "A1"
End Sub
VB

これは少し長いですが、やっていることはシンプルです。

  • 元データヘッダ → 列番号 の辞書を作る
  • レイアウト定義に従って「順番」と「表示／非表示」を決める
  • 新しい並びの配列を作ってシートに書き戻す

これで、「毎回順番が違う Excel」を「いつも同じ順番」に変換できます。
カラム名も ToHeader で変えられるので、「顧客ID → 顧客コード」のような名前変更も一緒にできます。

実行例：もらった生データを“業務標準のレイアウト”に落とし込む

実行の流れを 1 つにまとめた例

' ModLayout_Example.bas
Option Explicit

Public Sub Run_LayoutFix_Example()
    Dim ws As Worksheet
    Set ws = Worksheets("RawData")   ' もらった生データを貼るシートとする
    
    ' 1) 列並びとヘッダ名を揃える
    ReorderColumnsByLayout "RawData", "LayoutDef"
    
    ' 2) 標準レイアウト整形（フォント・罫線・数値書式）
    FixLayout_Standard "RawData"
    
    ' 3) 印刷レイアウト整形
    FixPrintLayout "RawData"
    
    MsgBox "レイアウト修正テンプレの例が完了しました。", vbInformation
End Sub
VB

この Sub をボタンに割り当てておけば、

  1. RawData にコピペ
  2. ボタン押す
  3. 列順・ヘッダ名・見た目・印刷設定が全部“社内標準の形”になる

という運用にできます。

落とし穴と対策（重要ポイントの深掘り）

CurrentRegion まかせで“余計な範囲”まで巻き込む問題

A1 の CurrentRegion は便利ですが、
右や下に「たまたま何か入力されているセル」があると、そこまで巻き込んでしまいます。

対策としては、

  • レイアウト整形をかける前に、「A列の最終行」「1行目の最終列」で自分で範囲を決める
  • もしくは「一度余計なセルを削除してから CurrentRegion を使う」

などがあります。
実務では「データ貼付け専用シート」を用意して、そこは“余計なものを絶対置かない”ルールにするのが楽です。

数値列の自動判定が完全ではないことを理解しておく

数値列判定は「データ行が全て数値 or 空なら数値列」としていますが、
たまに文字が混ざっていると数値書式がかからないことがあります。

大事なのは、「このロジックは完璧ではない」と理解したうえで、

  • よく使う標準レイアウトでは、明示的に列番号を指定して書式をかける
  • それ以外は“だいたい整う”くらいに期待値を下げる

という使い分けをすることです。

列並び定義（LayoutDef）と元データヘッダのズレ

FromHeader が 1文字でも違うと、「その列は並べ替え対象外」になります。
最初はそれで構いませんが、本番運用では

  • LayoutDef の FromHeader は「元データヘッダのままの表記」を使う
  • どう変えたいかは ToHeader に書く

というルールにしておくと混乱が減ります。

見た目とロジックを混ぜすぎない

レイアウトテンプレは、「見た目」に特化させるのがコツです。
計算ロジックやデータ加工まで一緒に入れ始めると、
「レイアウト修正したいだけなのに計算も走る」という重いマクロになってしまいます。

データ加工（クレンジング・集計）は別テンプレに分け、
レイアウト修正は“最後の化粧”の役割に留めておくと、全体の構造がシンプルになります。

まとめ：レイアウト修正も「部品化」して、どんな表も同じ“顔つき”にする

レイアウト修正テンプレのキモは、

  • フォント・罫線・数値書式・印刷設定を部品単位で用意しておく
  • 列並びは LayoutDef などの“定義表”で管理する
  • 「生データを貼る → ボタン1つで社内標準レイアウトにする」という流れを作る

ことです。

この型さえ作ってしまえば、
他部署からどんなぐちゃぐちゃな Excel が来ても、

「RawDataに貼る → レイアウトボタン → あとは分析や帳票生成に回す」

というシンプルな運用に変えられます。

スポンサーリンク
タイトルとURLをコピーしました