Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    rapidly remove 'merge and center'? (2003)

    I have inherited some workbooks from a person who had not read Hans' post on this topic (595,826 onwards). i want to copy several sheets onto one sheet, all the sheets have the same layout, so selecting the whole sheet and pasting in below the last row would work okay, except that some (not all) the sheets have merge and center applied to several columns, not just cells but the whole column. So I need to go through each sheet in turn, manually selecting and un-merging columns, before i can copy the sheets. Please tell me there is an easier way!

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: rapidly remove 'merge and center'? (2003)

    Is it not possible to select all the sheets, click on the button above the first row to select all ceels and then unmerge? It worked for me?
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: rapidly remove 'merge and center'? (2003)

    Added: Rudi's solution is simpler - no code needed!

    You could run this short macro:

    Sub UnMergeAll()
    Dim wsh As Worksheet
    For Each wsh In Worksheets
    wsh.Cells.UnMerge
    Next wsh
    End Sub

    If some sheets are protected, you'll have to a bit more work.

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    London, Gtr London, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: rapidly remove 'merge and center'? (2003)

    Rudi's solution is the one I tried first, however not all the sheets have merged cells, and the button is a toggle, so I get the warning about merging all the cells in a sheet. Selecting only the sheets with merged cells would be as time consuming as dealing with them one by one. I have several workbooks each with many sheets, so Hans' macro is just what I need. Thanks

Posting Permissions

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