Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    North Providence, Rhode Island, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to reduce Merge and Center

    I know there is a short cut to reduce the Merge and Center formatting after it has been done. Example: Let's assume I have a Title which I Merged and Centered across Col A-E when I really wanted A-D without deleting Col E.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    Not quite sure what you mean. However the following macro will merge selected cells, or unmerge them if they are already merged. It emulates what was the standard merge/unmerge button in XL95 which was far superior to the mess in XL97, in my opinion.

    Sub MergeToggle()
    '
    With Selection
    If .MergeCells Then
    .MergeCells = False
    Else
    .MergeCells = True
    .HorizontalAlignment = xlCenter

    End If
    End With

    If you select your merged A-E cells, then run the macro, then select A-D and run it again, you should achieve what you want.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    Ed just repeat the process but with the correct cells highlighted ie A - D.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    Actually XL95 did not have a Merge Cells facility, but rather Center Across Selection. The difference being that in CAS the actual cells are retained and can hold data, but entering data disturbs the CAS feature. When using merge cells, data can only be held in the first cell, which effectively replaces all cells that originally composed the range. Center Across Selection is still available in XL97 and 2000. To implement it go to Format Cells, Alignment, and from the Horizontal Dropdown list select Center Across Selection. The following macro does the same thing, and can be assigned to a macro button and used instead of merge cells where required. <pre>Sub CenterSelection()
    With Selection
    .HorizontalAlignment = xlCenterAcrossSelection
    .MergeCells = False
    End With
    End Sub</pre>

    It is just a feature that got hidden away when the Merge Cells facility was introduced.

    Andrew C

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Location
    North Providence, Rhode Island, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    Tried it and it does not work!! Thanks for the effort.

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    North Providence, Rhode Island, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    Thank you--will give it a try.

  7. #7
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    New Zealand
    Posts
    295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    Ok I just remembered. Click on the merged cells and right click inside the cell. Click on Format cells and the Alignment Tab. Down at the bottom you will see Merge cells ticked. Untick it and then repeat the process with the correct cells.

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    North Providence, Rhode Island, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    THAT'S IT!!!!!! Many thanks--I know I must have written a Macro to save the time and that's why I thought there was simply a key stoke to do it.

    Thanks again. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

  9. #9
    Lounger
    Join Date
    Oct 2002
    Location
    Mackay, Queensland, Australia
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    The other thing you could do is add the Unmerge Cells button to a toolbar. Right click an existing toolbar. Choose Customize|Commands Tab|Format. Locate the Unmerge Cells button and drag it to the toolbar you want it on. I hated Microsoft for changing the Centre Across Selection button to Merge and Centre but having this button readily available has alieviated my crossness. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    Andrew I am confused. If I put Fred, Mary, and Joe into cells A1, B1, & C1, then select A1 to C1, then use Format/Alignment/Horizontal/Centre Across Selection all that happens is that all three words become centred in their cells. If I put Fred in A1 and leave B1 and C1 empty, then select A1 to C1 etc, Fred appears centred across the three columns. Isn

  11. #11
    Star Lounger
    Join Date
    Jan 2001
    Location
    North Providence, Rhode Island, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    Thank you, Suzanna. Exactly what I was looking for in less than 48 hours. Woody's Lounge is a wealth of resources from a wealth of great people. Thanks <img src=/S/yep.gif border=0 alt=yep width=15 height=15>

    Ed

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: How to reduce Merge and Center

    Michael, I think that perhaps a little misunderstanding as I did say <but entering data disturbs the CAS feature>, but I should have been more specific perhaps. You can center data in A, center across A, B & C, but if you enter data in B or C that changes the centering. I just thought that may not have been aware that you could still do things the old way, as the feature was hidden away

    Andrew

Posting Permissions

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