Replace Merged Cells with Center Across Selection

Let the macro do the work

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 was a recorded macro that I added an If statement to.

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.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

2 thoughts on “Replace Merged Cells with Center Across Selection