How To Prevent Copy/Paste Merged Cell Errors [Solved!]
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 SubExamples 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.
Then right click on any of the highlighted cells in your selected range. Click on Format Cells…
Go to the Alignment tab and in the Horizontal drop down select Center Across Selection. Then click OK.
Your selection should now be perfectly centered across the multiple cells in your 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”.
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 SubThe 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.
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!
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![Free Example Data Sets For Spreadsheets [Instant Download]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/10/SpreadsheetSampleData.png)
Chris Newman
Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.