Unfortunately lots of people use the Merge & Center format in their spreadsheets. When working with other people’s files that contain Merged cells I will often remove the Merged cells format and apply Center Across Selection which is the preferred format to use. The macro below will convert Merged cells to Center Across Selection.
Note: Center is Excel’s US spelling.
The Merge and Center icon is on the Home ribbon
The limitation of this macro is that it works for row-based Merged cells, not column-based Merged cells. There is no alternative for column-based Merged cells – see image below.
The problem with Merged cells is that they limit, or affect, your ability to copy and paste ranges and they also can crash macros in certain circumstances.
The macro below works on the selected range. It is important to select the correct range. This macro is useful if you have inherited a sheet that has lots of Merged cells. It usually takes a few clicks to remove Merged cells and apply Center Across Selection. The macro makes it easy.
(If you are unsure how to use macros – read this blog post)
Sub MergeToCenteAcross() 'Removes Merged cells format and applies Center Across Selection If TypeName(Selection) = "Range" And Selection.Rows.Count = 1 Then Selection.UnMerge Selection.HorizontalAlignment = xlCenterAcrossSelection End If End Sub
This started out as a recorded macro and I added an If statement.
As with much VBA code you can read this code and get a good understand what is happening.
Selection is a key word that describes what is selected when you run the macro. Because Selection could be a chart or a column I have vetted it with the If statement, making sure it is a range and only has one row. Selection is a very powerful keyword and I use it frequently.
The two lines that start with Selection were both recorded.