VBA応用(シート上の結合セルを見つける)

結合セルがあると、マクロから見ると結構不便だったりします。

シートのデザイン上で「結合セル」はよく使うものです。セルに登録する内容を「データ」として意識せずに、単に「ワープロ」的イメージでシートをデザインしている例はたくさん見ることができます。皆さんも、自分の会社にあるExcelで作られたドキュメントで、「見かけ」や「印刷」のことしか考えていないデザインのワークシートをよく見るのではないでしょうか。でも、いざ、マクロが使えることが判ると、これらを「データ」として扱おうとし始めます。この段階になって「結合セル」がマクロ上で「やりにくい存在」だということを思い知らされるはずです。

最近、この手(「結合セル」に関すること)の質問が多いのです。

こんなことはありませんか？

バラバラに多数作られているドキュメントがあるフォルダに収容されているので、これらから管理する一覧表を自動的に作成したい。
作成済みの多数作られているドキュメントを、一括処理で書式を変更したい。

多少、マクロが理解できていても、これらのことをやろうとするのに「結合セル」に悩まされたことはありませんか？

ここでは、その「結合セル」の扱いを理解するためにサンプルを用意してみました。

(画像をクリックすると、このサンプルがダウンロードできます)
こんな状態の何でもないシートです。

ちょうど、「結合セル」が選択された状態になっています。選択されていれば「こっちのもの」で、イミディエイトウィンドウを開いて、
「結合セル」のアドレスを取得する。

このように入力すれば、「結合セル」の実際のアドレスがつかめます。
そこで、ここでつかんだアドレスを利用して、
「結合セル」に値を入れてみる。

と入力してみると、

このように値が入力できました。
でも、これを「結合セル」の左上位置でないセルを指して値をセットすると、
「結合セル」に値を入れてみる。

シート上の表示値は以前と変わっていないことが判ると思います。
このように、「結合セル」はマクロから操作する場合には都合が悪いものです。このように値の操作ならエラーにはなりませんが、書式などの操作になると実行時エラーで止まってしまうこともあります。

では、どうしたら「結合セル」をうまく扱えるのでしょうか。

まず、あるセルが「結合セル」に含まれているかどうかは、「MergeCellsプロパティ」で判ります。その上で、そのセルが属している「結合セル」は、「MergeAreaプロパティ」が返してくれます。この「MergeAreaプロパティ」やシートの使われたセル範囲を取得する「UsedRangeプロパティ」などは、「Rangeプロパティ」と同じ「Rangeオブジェクトを返すプロパティ」です。
これらのことから、シートの使用領域(UsedRange)の中をループさせて判別するサンプルがこちらです。


'***************************************************************************************************
'   結合セルを見つけてイミディエイトに表示するサンプル              Module1(Module)
'
'   作成者:井上治  URL:https://www.ne.jp/asahi/excel/inoue/ [Excelでお仕事!]
'***************************************************************************************************
'変更日付 Rev  変更履歴内容------------------------------------------------------------------------>
'05/11/02(1.00)新規作成
'20/02/22(1.10)*.xlsm化、他
'***************************************************************************************************
Option Explicit

'***************************************************************************************************
'   ■■■ ワークシート側からの呼び出し処理 ■■■
'***************************************************************************************************
'* 処理名　：FindMergeCells1
'* 機能　　：結合セルを見つけてイミディエイトに表示するサンプル①
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2005年11月02日
'* 作成者　：井上　治
'* 更新日　：2020年02月22日
'* 更新者　：井上　治
'* 機能説明：
'* 注意事項：
'***************************************************************************************************
Sub FindMergeCells1()
    '-----------------------------------------------------------------------------------------------
    Dim objR As Range                                               ' セル探索用Object
    ' まずシートの使用域を表示
    Debug.Print ActiveSheet.UsedRange.Address
    ' シートの使用領域内の各セルを探索するループ処理
    For Each objR In ActiveSheet.UsedRange
        ' このセルが結合されたセルかを判定(=True)
        If objR.MergeCells Then
            ' アドレスをイミディエイトに表示
            Debug.Print objR.Address & " " & objR.MergeArea.Address
        End If
    Next objR
End Sub

「結合セル」のアドレスをDebug.Printで表示させるだけのサンプルなので、イミディエイトウィンドウを開いて実行させてみてください。

イミディエイトにマクロの結果が表示された。

このような結果になりました。
最初の「$A$1:$F$11」がUsedRangeプロパティが返すセル範囲のアドレスです。以降が、ループで検査して「結合セル」に属すると判断されたセルのアドレスと、その「結合セル」のアドレスをスペースで挟んで表示しています。

さらに「ダブリ」を排除してみます。

上のサンプルでは、使用領域内の全セルを順次ループして判断させているので、たとえば「$B$3:$D$5」は9個のセルの結合なので9回表示されてしまいます。「シート内の結合セルのアドレスを表示」という目的であれば、同じアドレスが何回も出てきては不都合です。ですが、セルのループ処理の中で、「結合セル」が1回しかカウントされないようなループの方法はありませんから、検出したアドレスを一旦配列に格納して、重複しないようにチェックすることにします。


'***************************************************************************************************
'* 処理名　：FindMergeCells2
'* 機能　　：結合セルを見つけてイミディエイトに表示するサンプル②
'---------------------------------------------------------------------------------------------------
'* 返り値　：(なし)
'* 引数　　：(なし)
'---------------------------------------------------------------------------------------------------
'* 作成日　：2005年11月02日
'* 作成者　：井上　治
'* 更新日　：2020年02月22日
'* 更新者　：井上　治
'* 機能説明：重複除去対応
'* 注意事項：
'***************************************************************************************************
Sub FindMergeCells2()
    '-----------------------------------------------------------------------------------------------
    Dim objR As Range                                               ' セル探索用Object
    Dim lngIx As Long                                               ' テーブルINDEX
    Dim lngIx2 As Long                                              ' テーブルINDEX(Work)
    Dim strAddress As String                                        ' 結合セルのアドレス
    Dim tblAddress() As String                                      ' アドレス格納テーブル
    ' まずシートの使用域を表示
    Debug.Print ActiveSheet.UsedRange.Address
    ' 格納テーブルのインデックス初期値(-1は未格納の意)
    lngIx = -1
    ' シートの使用領域内の各セルを探索するループ処理
    For Each objR In ActiveSheet.UsedRange
        ' このセルが結合されたセルかを判定(=True)
        If objR.MergeCells Then
            ' 一旦アドレスを変数に格納(オブジェクトに何度も聞かないため)
            strAddress = objR.MergeArea.Address
            lngIx2 = 0
            ' この結合セルのアドレスが既にテーブルにあるかを確認
            Do While lngIx2 <= lngIx
                If tblAddress(lngIx2) = strAddress Then Exit Do
                lngIx2 = lngIx2 + 1
            Loop
            ' テーブルになければテーブルに追加
            If lngIx2 > lngIx Then
                lngIx = lngIx2
                ReDim Preserve tblAddress(lngIx)
                tblAddress(lngIx) = strAddress
            End If
        End If
    Next objR
    ' テーブルに格納したアドレスを順にイミディエイトに表示
    lngIx2 = 0
    Do While lngIx2 <= lngIx
        Debug.Print tblAddress(lngIx2)
        lngIx2 = lngIx2 + 1
    Loop
End Sub

'----------------------------------------<< End of Source >>----------------------------------------

このようなコードで、起動させると、

このように、UsedRange.Addressと、「結合セル」の2カ所のアドレスが1回ずつだけが表示されました。

シート上の結合セルを見つける。

結合セルがあると、マクロから見ると結構不便だったりします。