vba - 合并、取消合并、重新合并宏

Question

我正在开发一个宏来取消合并给定范围内的合并单元格，然后重新合并未合并的原始合并单元格。我一直在努力确定如何存储最初未合并的单元格列表，以便宏可以重新合并这些确切的单元格。电子表格中合并的行每周都会变化。

Sub MergeUnmerge()

'

Mergeunmerge Macro
'

Dim mergelist As Range

Dim celllist As Range


For Each cell In Range("A1:S49")

If cell.MergeCells = True Then
   Set mergelist = celllist
            cell.UnMerge
        End If
    Next

 For Each cell In mergelist
    Range("celllist").Merge
Next

结束子

Answer 1

您需要将 MergeArea 地址添加到数组中。

Sub MergeUnmerge()

Dim cel As Range
Dim mergeArr()  
y = 0

For Each cel In Range("A1:S49")
    If cel.MergeCells = True Then
        ReDim Preserve mergeArr(y + 1)
        mergeArr(y + 1) = cel.MergeArea.Address
        cel.UnMerge
        y = y + 1
    End If
Next cel

For x = 1 To y
    Range(mergeArr(x)).Merge
Next x

End Sub