How to reduce Merge and Center

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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.

Sub CenterSelection()
    With Selection
        .HorizontalAlignment = xlCenterAcrossSelection
        .MergeCells = False
    End With
End Sub

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

Andrew C

Reply | Quote

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

Reply | Quote

Michael, I think that perhaps a little misunderstanding as I did say , 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

Reply | Quote

Thank you–will give it a try.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Ed

Reply | Quote