How to unmerge all merged cell and fill them with the merged values?

Bumba · #1 11-09-2019, 09:05 AM

I have a workbook that has merged cells in it.

How do I unmerge all the merge cells in that workbook and fill those unmerged cells with the value its parent merge cell had in a different workbook in the same location using VBA.

I know to unmerge all cells in sheet I can use:

Quote:

ActiveSheet.Cells.UnMerge

But how to do the rest?
To better understand I have attached the merged file & unmerged file(final output file).

Please help.

p45cal · #2 11-10-2019, 11:36 AM

try:

Code:

Sub blah()
For Each cll In ActiveSheet.UsedRange.Cells
  If cll.MergeCells Then
    myValue = Empty
    myFormat = Empty
    Set mergedrange = cll.MergeArea
    mergedrange.UnMerge
    For Each celle In mergedrange.Cells
      If IsEmpty(celle.Value) Then
        celle.Value = myValue
        celle.NumberFormat = myFormat
      Else
        myValue = celle.Value
        myFormat = celle.NumberFormat
      End If
    Next celle
  End If
Next cll
End Sub

Note that this puts a 'hard' value (not a formula) in all but the first cell in merged areas.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How to vertically align a merged cell to fit text	paik1002	Excel	6	09-20-2016 03:00 AM
Save merged document as concatenated merge field values	texas791	Word VBA	4	02-25-2014 07:35 PM
Formatting Merged Time Values Between Excel & Word	JennEx	Mail Merge	1	07-12-2012 02:54 PM
How do I reference a merged cell in a multi column & row table in MS Word ('03')?	jihanemo	Word Tables	0	03-18-2009 08:33 AM