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 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.


Related Posts

Posted on
Categories: Excel Blog, Macros

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 *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

4 thoughts on “Replace Merged Cells with Center Across Selection”

  2. I modified this code a bit….now you can highlight a large selection then it will convert every merged cell in that selection

    Sub ConvertMerge()
    ‘Changes all cells in the selection that are merged to Center Across Selection
    Dim r As Range
    Dim rng As Range
    Dim newrng As Range
    Set r = Selection

    With r
    For Each cell In r
    If cell.MergeCells = True Then
    ‘MsgBox cell.Address & ” is merged”
    Y = Y + 1
    Set rng = cell.MergeArea
    Set rngStart = rng.Cells(1, 1)
    Set rngEnd = rng.Cells(rng.Rows.Count, rng.Columns.Count)
    newrng = Range(rngStart.Address, rngEnd.Address).Select
    Selection.UnMerge
    Selection.HorizontalAlignment = xlCenterAcrossSelection
    End If
    Next cell
    End With
    MsgBox (Y & ” merged cells converted”)
    End Sub

    Reply

    • Hi Kurt

      Thanks for your code – I have tweaked it to shorten it.

      Sub ConvertMerge()
      ‘Changes all cells in the selection that are merged to Center Across Selection
      ‘Doesn’t work with vertical merged cells
      Dim Cell, Y, c
      Dim rng As Range

      For Each Cell In Selection

      If Cell.MergeCells = True Then
      Y = Y + 1
      Set rng = Cell.MergeArea
      rng.UnMerge
      rng.HorizontalAlignment = xlCenterAcrossSelection
      End If

      Next Cell

      MsgBox (Y & ” merged ranges converted”)

      End Sub

      Note: you don’t have to select a range to work with it.

      Thanks
      Neale

      Reply