本ページには広告が含まれています。

Excel VBA 結合セルの解除と解除後の空白セルへの代入処理

Excel VBA 結合セルの解除と解除後の空白セルへの代入処理
2021.02.17

結合セルを含む範囲で並べ替えをしようとすると「この操作を行うには、すべての結合セルを同じサイズにする必要があります。」というエラーが表示されます。

ソート範囲に結合セルがあると表示されるメッセージ

このような、やろうとした処理がはじかれてしまう場面に遭遇したことのない方はいないと思います。そんな場合は、セル結合を解除して処理する必要がありますが、手動で対応できないくらいの結合セルだらけの場合は、VBAマクロで解除しましょう。

くるみこ
くるみこ

というわけで、結合セルを操作するマクロを解説します。

まず今回は、「解除編」として結合セルを解除する方法です。

「結合セル」のあるあるですね！「解除編」ということは、他にもあるということですね！　楽しみです。よろしくお願いしますm(_ _)m

【この記事でわかることは】
・Excelの結合セルを解除するマクロ
・解除した結合セルのデータを操作する方法

前回記事おさらいは、下のカードをクリックすれば開きます(^^ゞ

Search Analytics for Sheetsでデータを自動取得して分析しよう
スプレッドシートのアドオン「Search Analytics for Sheets」でサーチコンソールの詳細データを簡単に出力できます。インストール方法や使い方を解説しています。自動取得の方法やデータの活用方法なども少しだけ紹介しています。
www.kurumico.com
くるみこ
くるみこ

前回記事はExcel VBA じゃやなくて、Googleスプレッドシートの記事でした。

よかったら覗いてみてね(^^)/

スポンサーリンク

Excelの結合セルについて

Excelワークシートをデータベースとして使いたい場合、表の中にあるセルは結合しないようにしましょう。表の途中に結合セルがあった場合、次のような操作を行う場合に支障が出てしまいます。

・並び替え
・オートフィルター
・VLOOKUPなどの関数
・ピボットテーブルの作成
・グラフを作成する場合
・行単位で処理をしていくようなマクロ
・コピー＆ペースト
・行の追加や削除
・その他・・・

結合されたセルが存在するワークシートをデータベースとして活用するには、まずはセル結合を解除する必要があります。

結合セルの性質

・セルを結合する場合、選択した範囲に複数のデータがあった場合下図ようなメッセージが出ます。

セル結合時、選択した範囲に複数のデータがあった場合表示されるメッセージ

・「OK」を選択した場合、選択範囲の左上（左上が空欄の場合は次のセル）の値になります。
・結合したセルを「解除」した場合、左上セルだけにデータが残り、他のセルは空欄です。
・データベースとして活用するには、この空欄にデータを埋める必要があることがわかります。

結合セルを解除した場合の文字列表示

こんな感じです(^^;

これを踏まえてコーディングを考えていきます。

スポンサーリンク

結合セルを解除して空欄を同一データで埋める

ロジックを考えます

【解除するだけなら、こんな感じ】
・セル範囲を指定する。
・セル範囲内の結合セルを探す。（範囲をループ処理）
・結合セルが見つかった場合、結合を解除する。（範囲全部を調べ終わるま繰り返す）

【データ代入もするなら次を追加】
・解除後の空欄セル（結合エリア）に先頭セルの値を一括代入します。

・セル範囲の指定方法はいろいろあるけど、ひとまず「UsedRange」で使用範囲全部にします。
・範囲を指定させるなら、「Applicatin.InputBox」を使います。
・結合セルを探すのは、RangeオブジェクトのMergeCellsプロパティで探します。
　セルが結合されていた場合、「True」論理値が帰ります。
・結合セルの解除は、RangeオブジェクトのMargeAreaプロパティの「UnMerge」で解除します。
　MergeAreaは、結合されているセル範囲を表すRangeオブジェクトを返します。
・値の代入は、「MergeArea」の先頭セルの値を「MergeArea」から返されたセル範囲に一括で代入します。

結合セルを解除するだけのコード

・この場合は、解除後先頭セルだけにデータが入り、他は空白セルになります。

'結合されているセルを調べて解除する
Sub UnMergeCells()
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange
        'UsedRangeの範囲内を全て調べる
        With rng
            If .MergeCells Then
                '結合セルだった場合
                With .MergeArea
                    .UnMerge '結合を解除
                End With
            End If
        End With
    Next
End Sub

結合セルを解除後に空欄を同一データで埋めるコード

・こちらは「空白セル」の部分に一括で「先頭セル」データを代入します。

'結合セルを解除して解除後の空欄に値を代入する
Sub UnMergeCellsFill()
    Dim rng As Range
    For Each rng In ActiveSheet.UsedRange
        'UsedRangeの範囲内を全て調べる
        With rng
            If .MergeCells Then
                '結合セルだった場合
                With .MergeArea
                    .UnMerge '結合を解除
                    '先頭セルの値をMergeAreaに代入
                    .Value = .Resize(1, 1).Value
                End With
            End If
        End With
    Next
End Sub

・範囲を「UsedRange」とした場合、範囲内の全の結合セルを解除してしまいます。
・解除したくない「セル範囲」があった場合は問題になります。
・また、場合によっては「UsedRange」が広範囲の場合時間がかかってしまいます。

Application.InputBoxを使って範囲指定

・Application.InputBoxを使って範囲指定できるようにしてみましょう。

'ApplicationInputBoxで範囲選択できるようにする
'UsedRangeも選択できるようにしておく
Sub UnMergeCellsFillSelection()
    Dim rng As Range
    Dim sRng As Range
    Dim res As Long
    'MsgBoxで範囲を選択するかどうか確認する
    res = MsgBox("対象範囲を選択しますか？" & vbCrLf & _
                "「いいえ」= UsedRangeが対象になります", vbYesNo, "動作選択")
    If res = vbYes Then
        '「はい」の場合Application.InputBoxを表示
        On Error Resume Next
        Set sRng = Application.InputBox( _
                    Prompt:="対象セル範囲を選択指定してください！" _
                    , Title:="範囲選択", Type:=8)
        On Error GoTo 0
        If sRng Is Nothing Then Exit Sub
    Else: Set sRng = ActiveSheet.UsedRange '「いいえ」の場合はUsedRange
    End If
    Application.ScreenUpdating = False '画面更新を中止させておく
    For Each rng In sRng
        With rng
            If .MergeCells Then
                With .MergeArea
                    .UnMerge
                    .Value = .Resize(1, 1).Value
                End With
            End If
        End With
    Next
    Application.ScreenUpdating = True '画面更新を再開
End Sub
【コード誤り修正しました】2021/12/10追記
18行目　Else: sRng = ActiveSheet.UsedRange '「いいえ」の場合はUsedRange
Rangeオブジェクトの指定方法「Set」が抜けていました。
修正後　Else: Set sRng = ActiveSheet.UsedRange '「いいえ」の場合はUsedRange
※ご指摘のご連絡をいただきありがとうございました。サンプルファイルも修正しました。

・範囲を選択指定できれば、活用幅が広がると思います。
・実行速度も無駄がはぶかれる分速くなります。
・解除後の内部セルは、罫線が引かれていないので、罫線を引くコードを追加してもよさそうです。

まとめ（おわりに）

・いかがでしたでしょうか？
・「いつも汎用でだれでも使えて活用できるように考えてvbaを使う」というポリシーを念頭に今回の記事も書いたつもりです。
・実行例のイメージが掴みづらいので「GIF画像」も配置しました。
・サンプルファイルを用意していますのでよろしければお使いください(^^)

まとめと感想など

くるみこ
くるみこ

今回の解説はいかがでしたか？　そんなに難しくなかったでしょう？
「MergeCells」で結合セルを探して処理するという流れでしたね。

はい！ わかりやすかったです(^^)
罫線が消えているセルに罫線を書き入れる部分の処理を自分で考えて書いてみたいと思います。　

くるみこ
くるみこ

えらい！　頑張ってみてね！
今回は解除だったので、次回は「結合する」方を考えてみますね(^^)/

【今回分かったことは】
・結合セルはRangeオブジェクトのMergeCellsプロパティで探します。
・結合セルの解除は、RangeオブジェクトのMergeAreaプロパティの「UnMerge」で解除します。
・「MergeArea」は、結合されているセル範囲を表すRangeオブジェクトを返すので、それを使って、先頭セルの値を取得して範囲に一括代入できます。

【お決まりの注意事項】
・マクロ（VBA）を実行する際は必ずバックアップを取ってから行ってください！
・マクロ（VBA）は実行後にファイルを保存すると元に戻すことはできません！
・実行後にファイルを保存せず終了すれば、実行前に戻すことができます！

★★★ ブログランキング参加中！ クリックしてね(^^)/ ★★★

にほんブログ村 IT技術ブログ VBAへ
にほんブログ村


Visual Basicランキング

今後の記事について

今回の記事はいかがだったでしょうか。皆さまのお役に立てたなら幸いです(^^;
「汎用でだれでも使えて活用できるように考えてvbaを使う」というポリシーで、記事を継続して書いていきたいと思っています。どうぞよろしくお願いしますm(_ _)m

【検討中の今後の記事内容は・・・・・】
・実務に役立つものを提供できるよう常に検討しています(^^ゞ
・その他雑記的に「プチネタなど」もいろいろ考えていきたいと思います・・・・・
・今後の記事にもご期待ください(^^)/

スポンサーリンク
スポンサーリンク

記事のサンプルファイルをダウンロードできます

今回の記事のサンプルはこのリンク先からダウンロードできます！

過去の記事で使用したサンプルファイルがダウンロードできるページを設置しています
こちら（このリンク先）からご利用ください

【Excel VBA】サンプルファイル_ダウンロードのページ
【Excel VBA】の記事内で使ったサンプルファイルを（Zipファイルで）ダウンロードできるようにしました。記事内では細かい部分の解説が不十分ですが、実際にファイルをダウンロードして実行することで使えるのかどうか見ていただけたら幸いです。
www.kurumico.com

スポンサーリンク

スポンサーリンク