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

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-09-2019, 09:05 AM
Bumba Bumba is offline How to unmerge all merged cell and fill them with the merged values? Windows 7 32bit How to unmerge all merged cell and fill them with the merged values? Office 2007
Novice
How to unmerge all merged cell and fill them with the merged values?
 
Join Date: Jan 2019
Posts: 18
Bumba is on a distinguished road
Default How to unmerge all merged cell and fill them with the merged values?

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.
Attached Files
File Type: xlsx merged DATA.xlsx (8.9 KB, 2 views)
File Type: xlsx unmerged DATA.xlsx (8.9 KB, 2 views)
Reply With Quote
  #2  
Old 11-10-2019, 11:36 AM
p45cal p45cal is offline How to unmerge all merged cell and fill them with the merged values? Windows 10 How to unmerge all merged cell and fill them with the merged values? Office 2019
Expert
 
Join Date: Apr 2014
Posts: 318
p45cal has a spectacular aura aboutp45cal has a spectacular aura aboutp45cal has a spectacular aura about
Default

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.
Reply With Quote
Reply

Tags
vba excel

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to unmerge all merged cell and fill them with the merged values? 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


All times are GMT -7. The time now is 07:53 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft