Unable to Unmerge Cells Using Excel VBA

Question

I have data in an Excel worksheet. Four of the columns have merged cells. I have to unmerge two columns ("A" and "D") and put data in the unmerged cells based on certain conditions. My attempt to unmerge specific column using following code did not unmerge the cells or return any error message

If sheet5.Range("A1", "A2783").MergeCells Then 
sheet5.Range("A1", "A2783").MergeCells.UnMerge

Then I tried this

If ActiveSheet.Cells.MergeCells Then 
sht.Cells.UnMerge

but that also did not unmerge the cells or return any error message.

Any pointers please?

Answer 1

You want to set the MergeCells property to False:

sheet5.Range("A1", "A2783").MergeCells = False

Answer 2

The UnMerge method should be called on the Range object returned by the MergeArea property of a Range object.

Also,

The MergeArea property only works on a single-cell range. so write:

If sheet5.Range("A1").MergeCells Then 
sheet5.Range("A1").MergeArea.UnMerge

instead.

Ref:

• https://docs.microsoft.com/en-us/office/vba/api/excel.range.mergearea
• https://docs.microsoft.com/en-us/office/vba/api/excel.range.unmerge