Prevent Copy/Paste Merged Cell Errors

Center Across Selection and Prevent Copy Merged Cell Errors

What Is Center Across Selection?

If you are like most people (me included for quite some time), I suspect you have never heard of the option to center across your selection.  This feature is a cell horizontal alignment option in the Format Cells dialog box.  It basically mimics the look of merging and centering text, however you have more flexibility in the structural integrity of your worksheet cells.

Why Should You Use Center Across Selection?

The benefit (and it's a HUGE one) is that you will never have copy/paste problems when cells are centered across instead of merged.  Have you ever received the below error message?

This usually occurs when you try to PasteSpecial Values Only over-top of cells that are merged.  In order to remedy this, you have to figure out where the merged cells are and unmerge them (usually messing up your formatting).  This is probably one of my biggest annoyances as an analyst!  Using Center Across Selection gives you the formatting look that you want along with a guarantee that you can use any type of PasteSpecial over-top of the cells.

If you are a VBA coder you should know that merged cells are macro killers!  If you try to reference a cell and that cell is part of a merged range, Excel flips out!  A work around for this is to add an If/Then statement to see if the cell you are targeting is part of a merged range.  Then you can either exit the subroutine or unmerge the cells.  An example of this is shown below:

Sub MergedCell_Test()

'PURPOSE: Unmerge selection if merged
'SOURCE: www.TheSpreadsheetGuru.com

If Selection.MergeCells = True Then Selection.MergeCells = False

End Sub

Examples Of How To Center Across Selection

Cell Format Option

First you will need to select the range (can only span across a row) of cells you wish to center across with the text you want to center in the first cell (farthest left) of the range.

Prevent Copy Paste Merged Cell Errors by Center Across Selection

Then right click on any of the highlighted cells in your selected range.  Click on Format Cells…

Prevent Copy Paste Merged Cell Errors by Center Across Selection

Go to the Alignment tab and in the Horizontal drop down select Center Across Selection.  Then click OK.

Prevent Copy Paste Merged Cell Errors by Center Across Selection

Your selection should now be perfectly centered across the multiple cells in your selection. 

Prevent Copy Paste Merged Cell Errors by Center Across Selection

Notice that unlike merging cells you can still select each individual cell.  Also note that your text will only show up (and therefore can only be edited) in its original cell.  In this example only Cell A1 would have the value of “My Sample Title”.

Prevent Copy Paste Merged Cell Errors by Center Across Selection

VBA Option

Below is the way I use VBA to center across my selections.  This is one of my most heavily used personal macros and I highly recommend you add this to your Quick Access Toolbar (QAT).  The great thing about this code is that it mimics an undo functionality.  What I mean by that is that if you accidentally run the macro to center across your selection you can run it again to reverse it!  I have started to try and add this functionality to some of my simple macros that I use very often.  Very nifty and very useful.

Sub Center_Across_Selection()

'PURPOSE: Center text across selection
'SOURCE: www.TheSpreadsheetGuru.com

    With Selection
        If .HorizontalAlignment = xlCenterAcrossSelection Then
            .HorizontalAlignment = xlGeneral
        Else
            .HorizontalAlignment = xlCenterAcrossSelection
            .VerticalAlignment = xlCenter
        End If
    End With

End Sub

The Center Across Add-in

I think Center Across is such a vital functionality, that I took the time to create a free add-in for you that will add a Center Across Selection button to your Home tab in Excel. You can check it and learn how to download it by visiting my post entitled How To Add A Center Across Selection Button To Excel's Home Ribbon Tab.

Center Across Excel Add-in

I Want To Hear From You!

Did you know about this feature?  Is it a pet-peeve of yours?  Let me know in the comments section below.  Feel free to share this post and spread the word to others!  Maybe one day Center Across Selection will be the norm and Merge & Center will be unheard of!