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サンプル集」の記事

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


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

ユーザーに絶対に停止させたくない場合のVBA設定|VBA技術解説(4月1日)
CharactersプロパティとCharactersオブジェクト|VBA技術解説(3月31日)
指数近似/対数近似/累乗近似(掲載順位とCTR)|エクセル関数超技(3月31日)
練習問題32(連続数値部分を取り出し記号で連結)|VBA練習問題(3月24日)
連続数値部分を取り出し記号で連結|エクセル関数超技(3月24日)
数式バーの高さを数式の行数で自動設定|VBAサンプル集(3月21日)
LET関数(数式で変数を使う)|エクセル入門(3月21日)
スピルに対応したXSPLITユーザー定義関数(文字区切り)|VBAサンプル集(3月15日)
XMATCH関数(範囲から値を検索し一致する相対位置)|エクセル入門(3月14日)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)|エクセル入門(3月14日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.繰り返し処理(For Next)|VBA入門
9.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
10.セルに文字を入れるとは(Range,Value)|VBA入門




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


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



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