VBAサンプル集
セル結合/解除でセル値を退避/回復

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日:2020-02-16

セル結合/解除でセル値を退避/回復


セル結合の弊害はネットに溢れているのでここで改めて説明の必要はないでしょう。
とはいえ、セル結合したい場合もありますよね、人間だからね。


VBAで適切に処理すればセル結合もきちんと処理は可能です。

第85回.結合セルの扱い|VBA入門
セルが結合されていると、マクロでは時に扱いづらい事があります、セル結合されている場合に、VBAでどのように取り扱うかを解説します。そもそも、やたらにセル結合すべきではないのですが、見た目重視で作られたシートでは、セル結合が頻繁に使用されているものです。

しかし、セル結合した時にどうしようもないのが、各セル値が失われてしまうことです。
セル結合すると、先頭セルの値だけが残り他のセル値は消えてしまいます。
これは、どうしようもありません。
そこで、セル結合した時にセル値をどこかに退避しておき、セル結合を解除した時に復元するVBAを考えてみました。


とはいえ、実際にこのVBAが必要になる事はほとんどないでしょう。
VBAを扱う人なら、セル結合があると面倒なことは承知しているはずですので、VBAで結合するという事自体が少ないでしょう。
さらに結合で失われる値を保持する必要がある場合などなかなか想定できません。
また、決してセル結合を推奨しているわけではないという事だけは申し上げておきます。
あくまで、セル結合等で失われてしまう情報の退避/回復方法のひとつとしてのサンプルVBAを提示してみたもになります。
従って、どちらかと言うとCustomDocumentPropertiesの使い方のサンプルになります。

セル結合/解除でセル値を退避/回復のVBA



'指定セル範囲をセル結合
Sub MergeRange(ByVal aRange As Range)
  If IsNull(aRange.MergeCells) Or aRange.MergeCells Then
    If MsgBox("結合セルが含まれています。" & vbLf & _
         "続行しますか?" & vbLf & vbLf & _
         "続行し場合、結合されているセルの値は失われます。", _
         vbYesNo + vbDefaultButton2, "確認") = vbNo Then
      Exit Sub
    End If
  End If
  Call StoreRange(aRange)
  Dim isDisplayAlerts As Boolean
  isDisplayAlerts = Application.DisplayAlerts
  Application.DisplayAlerts = False
  aRange.Merge
  Application.DisplayAlerts = isDisplayAlerts
End Sub

'指定セル範囲の先頭セルの結合範囲を解除
Sub UnMergeRange(ByVal aRange As Range)
  Set aRange = aRange.Item(1).MergeArea '先頭セル
  aRange.UnMerge
  Call RestoreRange(aRange)
  
  Dim wb As Workbook
  Set wb = aRange.Worksheet.Parent
  Dim myRange As Range
  For Each myRange In aRange
    Call DelCustomDocumentProperties(wb, myRange)
  Next
End Sub

'指定セル範囲をCustomDocumentPropertiesに退避
Sub StoreRange(ByVal aRange As Range)
  Dim myRange As Range
  For Each myRange In aRange
    Call AddCustomDocumentProperties(myRange)
  Next
End Sub

'指定セル範囲のValueとNumberFormatLocalをCustomDocumentPropertiesから復元
Sub RestoreRange(ByVal aRange As Range)
  Dim wb As Workbook
  Set wb = aRange.Worksheet.Parent
  
  Dim myRange As Range
  Dim sAddress As String
  For Each myRange In aRange
    If myRange.Address <> aRange.Item(1).Address Then '先頭セルは変更しない
      sAddress = aRange.Worksheet.Name & "!" & myRange.Address(False, False)
      myRange.Value = getCustomDocumentProperties(wb, sAddress & "_Value")
      myRange.NumberFormatLocal = _
        getCustomDocumentProperties(wb, sAddress & "_NumberFormatLocal")
    End If
  Next
End Sub

'指定文字列のCustomDocumentPropertiesを取得
Function getCustomDocumentProperties(ByVal wb As Workbook, _
                   ByVal aProperties As String) As String
  On Error Resume Next
  getCustomDocumentProperties = wb.CustomDocumentProperties(aProperties)
End Function

'指定セルのValueとNumberFormatLocalをCustomDocumentPropertiesへ退避
Sub AddCustomDocumentProperties(ByVal aRange As Range)
  Dim wb As Workbook
  Set wb = aRange.Worksheet.Parent
  
  Dim dps As DocumentProperties
  Dim sAddress As String
  Set dps = wb.CustomDocumentProperties
  sAddress = aRange.Worksheet.Name & "!" & aRange.Address(False, False)
  
  'CustomDocumentPropertiesから削除
  Call DelCustomDocumentProperties(wb, sAddress)
  
  'CustomDocumentPropertiesへ追加
  dps.Add sAddress & "_Value", False, msoPropertyTypeString, aRange.Value
  dps.Add sAddress & "_NumberFormatLocal", False, msoPropertyTypeString, aRange.NumberFormatLocal
End Sub

'指定セルのCustomDocumentPropertiesを削除
Sub DelCustomDocumentProperties(ByVal wb As Workbook, _
                ByVal aAddress As String)
  Dim dps As DocumentProperties
  Set dps = wb.CustomDocumentProperties
  Dim dp As DocumentProperty
  For Each dp In dps
    If dp.Name Like aAddress & "_*" Then
      dp.Delete
    End If
  Next
End Sub

CustomDocumentPropertiesについては、以下を参照してください。
ドキュメントプロパティ(BuiltinDocumentProperties,CustomDocumentProperties)
VBAで配列を必要とするのは、処理速度を上げる為だと言って良いでしょう。そもそも、エクセルにはセルの2次元配列であるシートがあります。にもかかわらず、VBAの学習を進めると必ず配列が出てきます。ではなぜVBAで配列必須になるかと言うと、セルを使うと処理速度が非常に遅く、これを高速に処理するために配列が必要となるからです。

上のVBAでは、ValueとNumberFormatLocalだけを扱っています。
罫線はプロパティが多くなるので大変ですが、必要なら適宜追加してください。
さすがに、条件付き書式や入力規則まで含めるとかなり難しくなってきます。
もっとも、結合解除した時に条件付き書式や入力規則をどうするかは色々と考えないといけない問題です。

また、結合解除した時に、先頭セルと同じ状態にしたい場合もあると思います。
そのような場合は、「RestoreRange」にオプション引数を追加するなりして、
aRange.Item(1)の情報を使うようにすれば良いでしょう。

セル結合/解除でセル値を退避/回復のVBAの使い方

Sub セル結合する()
  Call MergeRange(Range("D2:D4"))
End Sub

Sub セル結合を解除()
  Call UnMergeRange(Range("D2"))
End Sub

使い方は簡単なので問題はないでしょう。
登録されたCustomDocumentPropertiesの全削除や一覧は、この下に掲載しています。

退避したセル値の全削除と一覧出力

上のVBAでCustomDocumentPropertiesに退避した情報を全クリアしたい場合や、
CustomDocumentPropertiesの一覧を取得したい場合に使ってください。



Sub 使い方サンプル()
  Call CustomDocumentProperties2Sheet(ActiveWorkbook, ActiveSheet)
  Call AllDelCustomDocumentProperties(ActiveWorkbook)
End Sub

'CustomDocumentPropertiesを全削除
Sub AllDelCustomDocumentProperties(ByVal wb As Workbook)
  Dim dp As DocumentProperty
  For Each dp In wb.CustomDocumentProperties
    dp.Delete
  Next
End Sub

'CustomDocumentPropertiesの一覧をシート出力
Sub CustomDocumentProperties2Sheet(ByVal wb As Workbook, _
                  ByVal ws As Worksheet)
  Dim dps As DocumentProperties
  Dim dp As DocumentProperty
  Dim i As Long
  
  'Valueが定義エラーの場合の対応
  On Error Resume Next
  With ws
    .Cells.Clear
    .Range("A1") = "インデックス"
    .Range("B1") = "プロパティ名"
    .Range("C1") = "型"
    .Range("D1") = "値"
    i = 1
    Set dps = wb.CustomDocumentProperties
    For Each dp In dps
      .Cells(i + 1, 1) = i
      .Cells(i + 1, 2).Value = dp.Name
      .Cells(i + 1, 3).Value = dp.Type
      .Cells(i + 1, 4).Value = dp.Value
      i = i + 1
    Next
  End With
End Sub

一覧のシート出力では、シート全体をクリアしているので注意下ください。

セル結合/解除の最後に

セル結合は、確かに弊害が多くできれば使わない方が良いでしょう。
とはいえ、VBAを書く人の多くが他人の要望を受けて作成している場合は多いでしょう。
そんな時、どうしても断れずにセル結合せざる負えなくなってしまったときに、こんな方法もあるのだという事を思い出してもらえれば良いと思います。
ただし、セル結合せずに済むならそれが一番良い事は言うまでもありませんので、セル結合しないように良く話をしてみましょう。



同じテーマ「マクロVBAサンプル集」の記事

印刷ダイアログを使用する(xlDialogPrint)
名前定義の一覧と削除(Name)
シートを名前順に並べ替える
数式内の不要なシート名を削除する(HasFormula)
数式の参照しているセルを取得する
増殖した条件付き書式を整理統合する
条件付き書式で変更された書式を取得する
セル結合/解除でセル値を退避/回復
セル結合なんて絶対に許さないんだからね
セルの数式をネスト色分けしてコメント表示
セル結合して表を見やすくする(非推奨)


新着記事NEW ・・・新着記事一覧を見る

Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)
VBAのString型の最大文字数について|エクセル雑感(6月20日)
VBAで表やグラフをPowerPointへ貼り付ける|VBAサンプル集(6月19日)
アクティブシート以外の表示(Window)に関する設定|VBA技術解説(6月17日)
マクロ記録での色のマイナス数値について|エクセル雑感(6月16日)
ツイッター投稿用に文字数と特定文字で区切る|エクセル雑感(6月15日)
日付の謎:IsDateとCDate|エクセル雑感(6月14日)
IFステートメントの判定|エクセル雑感(6月13日)
インクリメンタルサーチの実装|ユーザーフォーム入門(6月12日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ