Results 1 to 7 of 7

unmerge and fill

Threaded View

  1. #1
    Registered User
    Join Date
    09-10-2015
    Location
    London
    MS-Off Ver
    365 at work, 2016 at home
    Posts
    79

    unmerge and fill

    Hi all,
    I currently have a solution for what I think is a fairly common problem, but because it loops, it takes forever.
    Is there a single operation way of doing this? it also only works for merges vertically, ideally we want both ways.

    Find all merged cells, unmerge them, and fill each range with the data that was merged and now only occupies top left.

    I've found that you can search for merged cells with find and replace, but it outputs a list of them, within the find dialog, I can't find a way to make them selected.

    There are other blank cells on the sheet which need to remain blank, some of which may be adjacent to merged cells, so I can't just unmerge then 'find blank' and make them all =R-1C.
    Edit: I've tried findblank to make all prexisting blanks read something like "blank" which (after the demerge+fill) I would then find and replace with actual blanks, but it inputs "blank" in all the merged cells too, even over-writing the data in those merged cells.
    Current
    Sub Demerge()
    
    LastRow = Range("H10000").End(xlUp).Row
    myColumn = 1
    
    Do Until myColumn = 26
    
        myRow = 7
        
        Do Until myRow = LastRow
        
            Cells(myRow, myColumn).Select
            If Cells(myRow, myColumn).MergeCells = True Then
                Cells(myRow, myColumn).UnMerge
                Selection.FillDown
            Else
            End If
        
            myRow = myRow + 1
        
        Loop
        
        myColumn = myColumn + 1
        
    Loop
    
    End Sub
    This only works for vertically merged only cells, which is fine for this version, and if we can't get a solution that does mixed vertical only merges, horizontal only merges and both-ways merges, then we will give up and go with a vertical only solution, but long term we're probably going to have to come back to it for something universal.
    Further edit: more googling brought me this, seems to solve the horizontal/vertical issue, but it still loops. Was really hoping for something like find and replace that would select just the merged cells and do them all in one go. Don't understand HOW this one works though...
    Sub UnMergeFill()
    
    Dim cell As Range, joinedCells As Range
    
    For Each cell In ThisWorkbook.ActiveSheet.UsedRange
        If cell.MergeCells Then
            Set joinedCells = cell.MergeArea
            cell.MergeCells = False
            joinedCells.Value = cell.Value
        End If
    Next
    
    End Sub
    Last edited by cheesemeister; 10-18-2018 at 10:03 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Mass unmerge
    By dewifarhana in forum Excel General
    Replies: 1
    Last Post: 11-03-2014, 11:52 PM
  2. [SOLVED] Unmerge Cells and Fill down the Values in newly inserted Column
    By prkhan56 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2014, 11:21 PM
  3. Replies: 8
    Last Post: 04-11-2014, 12:01 PM
  4. Commandbutton code to both erase cell contents, dismiss fill colour and unmerge
    By vdongen in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-03-2010, 10:14 AM
  5. Unmerge and fill down
    By dahrius in forum Excel General
    Replies: 5
    Last Post: 02-18-2010, 06:11 AM
  6. [SOLVED] How do you unmerge cells
    By Freddo in forum Excel General
    Replies: 1
    Last Post: 06-07-2006, 12:10 PM
  7. [SOLVED] Unmerge to all cells possible?
    By Dan von InfoPath in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-30-2006, 05:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1