arigirisu

【EXCEL VBA】結合セルの行高さ自動調整

—

by

arigirisu
in

ちょっと長めの文章をセルに入力していると文字がセルの幅からはみ出すことがあります。
こういうときは、「折り返して全体を表示する」という機能を使うと便利です。「行の高さの自動調整」も一緒にやってくれるのでとっても便利です。
ところが、結合されたセルに入力した場合は「行の高さの自動調整」が働きません。

結合されたセル（以下、[統合セル]という）を選択した状態で [ホーム] → [書式]→ [行の高さの自動調整] をクリックしてもダメです。
行番号の下の境目をダブルクリックしてもだめです。
つまり、EXCELには[結合セル]の行高さ自動調整の機能がないということです。
ということは、マクロの出番です。

マクロ作成の方針

[結合セル] を選択した状態で、いったん結合を解除します。
結合を解除すると、結合していたセルの左上のセルがActiveCellとなり、入力されていた文字は、そこに移動します。
そして、この状態なら「折り返して全体を表示する」「行の高さの自動調整」の機能が使えます。これを利用してマクロを作成します。
ActiveCellに対して「折り返して全体を表示する」を行う前に、ActiveCellの列幅を[結合セル]全体の列幅に置き換えておくことがポイントです。
もちろん、手動で行番号下の境目をドラッグして行幅を広げるのが簡単ですが、、、[結合セル]を右クリックして行の高さの自動調整ができるとかっこいいですよね。

手順

  1. 対象の[結合セル]の状況（行数、列数）を把握する
  2. 対象の[結合セル]全体の列幅を取得する（記憶しておく）
  3. 結合を解除する
  4. 解除後の左上のセル（この時点のActiveCell）を操作する
    1. ActiveCellの列幅を記憶しておいた[結合セル]全体の列幅にする
    2. 折り返して全体を表示する
    3. 行の高さの自動調整
    4. 自動調整した行高さを取得する（記憶しておく）
    5. 解除前のセル幅に戻す
  5. セルを結合して元に戻す

マクロ

Sub 結合セルの行高さ自動調整()
'対象の[結合セル]の状況把握
    Dim 結合セル As Range   '対象の[結合セル]
    Dim 対象行数 As Long    '結合セルに含まれる行数
    Dim 対象列数 As Long    '結合セルに含まれる列数
        Set 結合セル = Selection
        対象行数 = 結合セル.Rows.Count
        対象列数 = 結合セル.Columns.Count
'[結合セル]の列幅を取得する
    Dim 結合セルの列幅 As Double
        結合セルの列幅 = 0
    Dim 解除後のセル幅() As Double
    ReDim 解除後のセル幅(1 To 対象列数) As Double
    Dim i As Long
        i = 1
    Dim trgt As Range
    For Each trgt In 結合セル.Resize(1)
        解除後のセル幅(i) = trgt.ColumnWidth
        Debug.Print 解除後のセル幅(i)
        結合セルの列幅 = 結合セルの列幅 + 解除後のセル幅(i)
        i = i + 1
    Next
'セルの結合を解除する
    結合セル.UnMerge
' 解除後の左上セル(Activecell)を操作する
    With ActiveCell
        .ColumnWidth = 結合セルの列幅    '列幅を、[結合セル]の列幅にする
        .WrapText = True    '折り返して全体を表示する設定
        .Rows.AutoFit   '行高さ自動設定
        Dim 目標行高さ As Double
            目標行高さ = .RowHeight '自動設定した行高さを覚えておく
        .ColumnWidth = 解除後のセル幅(1)    '解除前のセル幅に戻す
    End With
'セルを結合して元に戻す
    With 結合セル
        .Merge
        .RowHeight = 目標行高さ / 対象行数
        .HorizontalAlignment = xlLeft
    End With
End Sub

マクロ解説と注意点

9～22行目：ColumnWigthプロパティを使う

ここでは、[結合セル]を構成してる1つ1つのセル幅を取得しながら、最終的に[結合セル]全体の列幅を計算しています。
なぜ、こんなことをするのか？
これは、セル幅を取得するプロパティに関係します。
セル幅を取得するプロパティには、ColumnWigthとWidthの2種類ありますが、戻り値の取得単位や対象範囲が異なります。

プロパティ列幅取得
戻り値の単位		列幅取得
範囲指定の場合		列幅設定
Widthピクセル指定範囲の合計幅できない
ColumnWidth文字数※
(※”0″を表示できる数)		・列幅が全て同じなら1セルの列幅
・列幅がバラバラならNull値		できる
(文字数で設定)

上の表でおわかりのように、Widthプロパティは「列幅設定」ができません。Widthプロパティには指定範囲の合計幅を取得できるメリットがあるので使いたいところですが、設定のときにはColumnWidthプロパティを使わないといけません。しかし、設定値の単位がネックです。
実は、ピクセルと文字数は簡単に変換できません。これは「文字数の幅」が「使用しているFont」に依存するからです。
というわけでColumnWidthプロパティを使います。

しかし、ColumnWidthプロパティには、指定範囲の合計幅を取得できないという欠点があります。
これを解決するために9～22行の記述のように、[結合セル]を構成する1つ1つのセル幅を取得して、[結合セル]全体のセル幅を計算しているというわけです。

24行目：結合セル解除後のActiveCellの位置について

前段の9～22行目までは、[結合セル]は結合したままです。24行目で、[結合セル]を解除します。
結合セルの解除は、UnMergeメソッドで行います。
解除すると、結合していたセル範囲の左上のセルがActiveCellになります。

26～33行目：ActiveCellを操作する

１．ActiveCellの列幅を記憶しておいた[結合セル]全体の列幅にする

27行目：ActiveCell.ColumnWidth = 結合セルの列幅

２．折り返して全体を表示する

28行目：ActiveCell.WrapText = True

３．行の高さの自動調整

29行目：ActiveCell.Rows.AutoFit

４．自動調整した行高さを取得する（記憶しておく）

30,31行目：
Dim 目標行高さ As Double
目標行高さ = ActiveCell.RowHeight

５．解除前のセル幅に戻す

Sub セル右クリックメニューに結合セルの行高さ自動調整を追加する()
    'いったんセルの右クリックメニューを初期化してから追加する
    Application.CommandBars("Cell").Reset
    With CommandBars("Cell").Controls.Add(Before:=1)
        .Caption = "結合セルの行高さ自動調整"
        .OnAction = "結合セルの行高さ自動調整"
    End With
End Sub