Merge and Unmerge Cells

Introduction (#intro)

Merging several columns of cell in rows is frequently necessary to design a spreadsheet form.  The following macros are designed to help merge and unmerge cells in a selection.  The major macro of importance would be MergeRxR below and UnMergeSelected.  Merging here is referring to the cell container itself not the text within.

During the testing of these macros some interesting observations were made on how cells are colored if you chose a column and color it -- the merged cells are not included in a column selection unless all cells in the merge are selected.

Another observation, if you are merging cells you will want to use cell borders (format, cells, borders), but if you unmerge cells you will lose the border in the newly revealed separations.

Merge Macros   (#macros)

The major macro here is MergeRxR which will merge the cells for each selected area, but with a twist.  A normal cell merge would merge cells for rows and columns.  Code has been added to process each row within a selection as if an additional selection.  This will make the selection process a lot faster as you need not worry about making separate selections for each row.

MergeRxR_Join is a variation of MergeRxR that will JOIN the contents of the cells to be merged before merging.

MergeCxC is a variation of MergeRxR that merges within the columns in the selected areas.

The macro UnMergeSelected.  is simply a macro for format, cells, alignment (tab), [off] Merge cells

The macro SetUpG20 is simply a quick setup for MarkCells on the join.htm page.

 

This page contains some VBA macros.  If you need assistance to install or to use a macro please refer to Getting Started with Macros.  For more depth see Install a Macro or User Defined Function  on my Formula page. Speed and efficiency considerations can be seen in Proper, and other Text changes and in Slow Response.

Merge Example   (#example)

Color has been included to show selection of separate ranges.  The middle table shows the results of using MergeRxR macro so that columns and not rows are merged.  The table on right shows the results of a standard Format, Cells, Alignment (tab), [x]Merge Cells

Merge Macros - Code   (#code)

The macros code for the macros on this page can be found at http://www.mvps.org/dmcritchie/excel/code/merge.txt

 

This page contains some VBA macros.  If you need assistance to install or to use a macro please refer to my  GetStarted  page for simpler instructions, or to my GetFormula  page for more extensive information on macros.

The icon Merge Across is similar to the macro MergeRxR, but MergeRxR will work with multiple selections which may make setting up a lot easier.

Option Explicit
'David McRitchie, http://www.mvps.org/dmcritchie/code/merge.txt 2002-05-31

Sub MergeRxR()
   '-- Merge cells in multiple selected areas Row by Row  ---
   '    limited to the usedrange  (Ctrl+End)
   ' D.McRitchie, 2002-05-31 in merge.htm
  Dim rng As Range
  Dim rw As Range, ix As Long
  Set rng = Intersect(Selection, ActiveSheet.UsedRange)
  If rng Is Nothing Then
     MsgBox "nothing in usedrange to be merged"
     GoTo done
  End If
  Dim i As Long, j As Long
  For i = 1 To Selection.Areas.Count
      For j = 1 To Selection.Areas(i).Rows.Count
        Application.DisplayAlerts = False
        Selection.Areas(i).Rows(j).MergeCells = True
        Application.DisplayAlerts = True
      Next
  Next
done:
End Sub
 
Sub MergeRxR_Join()
   '-- Merge cells in multiple selected areas Row by Row  ---
   '    limited to the usedrange  (Ctrl+End)
	 '   JOIN contents of cells before merging
   ' D.McRitchie, 2002-05-31 in merge.htm
  Dim Str As String, ii As Long
  Str = ""
  Dim rng As Range
  Dim rw As Range, ix As Long
  Set rng = Intersect(Selection, ActiveSheet.UsedRange)
  If rng Is Nothing Then
     MsgBox "nothing in usedrange to be merged"
     GoTo done
  End If
  Dim i As Long, j As Long
  For i = 1 To Selection.Areas.Count
      For j = 1 To Selection.Areas(i).Rows.Count
        Application.DisplayAlerts = False
        Str = ""   '... concatenate cells for this row in selection
        For ii = 1 To Selection.Areas(i).Rows(j).Columns.Count
          Str = Str & " " & Selection.Areas(i).Rows(j).Columns(ii)
        Next ii
        Str = Mid(Str, 2)
        Selection.Areas(i).Rows(j)(1) = Str
        Selection.Areas(i).Rows(j).MergeCells = True
        Application.DisplayAlerts = True
      Next
  Next
done:
End Sub
 
Sub MergeCxC()
   '-- Merge cells in multiple selected areas Column by Column ---
   '    limited to the usedrange  (Ctrl+End)
   ' D.McRitchie, 2002-05-31 in merge.htm
  Dim rng As Range
  Dim rw As Range, ix As Long
  Set rng = Intersect(Selection, ActiveSheet.UsedRange)
  If rng Is Nothing Then
     MsgBox "nothing in usedrange to be merged"
     GoTo done
  End If
  Dim i As Long, j As Long
  For i = 1 To Selection.Areas.Count
      For j = 1 To Selection.Areas(i).Columns.Count
        Application.DisplayAlerts = False
        Selection.Areas(i).Columns(j).MergeCells = True
        Application.DisplayAlerts = True
      Next
  Next
done:
End Sub

 
Sub UnMergeSelected()
   '-- same as  format, cells, alignment (tab),  (turn off) Merge Cells
   Selection.MergeCells = False
End Sub

Sub SetupG20()
   Cells.MergeCells = False
   Range("A1:G20").Select
   Application.Run "personal.xls!MarkCells"  'see join.htm
End Sub

Additional comments on MergeRxR macro (#mergeRxR)

The used range is cell A1 through the last cell (Ctrl+End). 
The used range can be identified as select A1 then extend with CTRL+SHIFT+END.
If you start with an empty sheet there is nothing in the used range.
This macro checks that the active cell is within the Used Range.

Forget the macro for a moment.  You merge cells vertically the same as merging horizontally.  You can do both at the same time.
In Excel 2000 at least, if you select multiple areas with the help of Ctrl each selection in the multiple selections will be processed separately when you merge cells

Format, cells, Alignment (tab), [x] Merge Cells

If merge cells is greyed out, it means there is a mixture of merged and unmerged cells, just hit it twice.

If merge D2:F40 you will get one big block of merged cells. But if you wanted D2:F2, D3:F3, D4:F4, etc. to be merged independently you could select those ranges independently with help of Ctrl and then do your merge with the menus.

The macro MergeRxR (Merge Row by Row) is designed to process merge cells in each selection per row, which is what most forms would need.

MergeEmpty_A (#mergeempty_A)

Sub MergeEmpty_A() 'David McRitchie, 2004-05-27, programming With Cells .HorizontalAlignment = xlGeneral .VerticalAlignment = xlTop End With On Error GoTo done Cells(Rows.Count, 2).End(xlUp).Activate ActiveCell.Offset(0, -1).Activate backthere: Range(ActiveCell.Address, _ ActiveCell.End(xlUp).Address).Select Selection.Merge ActiveCell.Offset(-1, 0).Activate GoTo backthere done: End Sub

A1 B1 C1 D1   B2 C2 D2   B3 C3 D3   B4 C4 D4 A5 B5 C5 D5   B6 C6 D6   B7 C7 D7   B8 C8 D8   B9 C9 D9 A10 B10 C10 D10   B11 C11 D11   B12 C12 D12

A1 B1 C1 D1 B2 C2 D2 B3 C3 D3 B4 C4 D4 A5 B5 C5 D5 B6 C6 D6 B7 C7 D7 B8 C8 D8 B9 C9 D9 A10 B10 C10 D10 B11 C11 D11 B12 C12 D12

Merge icons (#builtin)

Formatting icons (manually: Format, cells, alignment, horizontal and merge options)

-- Merge and Center	-- customize, commands, format
-- Merge Across	-- customize, commands, format
-- Merge Cells	-- customize, commands, format
-- Unmerge Cells	-- customize, commands, format

Merging of cells interfers with sorting, filtering, copying, and pasting along with interferring with autofit of row height and column widths (see "Center Across Selection" below and Related Area).

Problems with Merged Cells (#problems)

• Can't sort merged cells
• Realigning row height can be a problem. (see Jim Rech's solution in Related Area).

Alternatives to Merging Cells   (#alternatives)

"Center Across Selection" (button was dropped in Excel 2000) available via Format, cells, alignment, horizontal: Center Across Selection without the Merge option is applied row by row on a selection and does not merge a block of cells.  On the other hand, "Merge and Center" (same button as was dropped from Excel 2000) is a merge operation and will merge an entire block of cells into one entity and does interfere with sorting etc.

Adjusting column widths may be an altertive to merged cells to avoid "This operation requires the merged cells to be identically sized."

There are no builtin shortcut keys for merge/unmerge.

Related   (#related)

The macros on this page are based on my posting of 2002-05-31 in misc.

• Color Palette, the 56 palette colors in Excel
• Rearranging Data in Columns contains macros MarkCells and
• MarkSepAreas of interest in testing merging/unmerging columns.  In fact code form MarkSepAreas was used to help with MergeRxR described on this page. Both Markcells and MarkSepAreas were used for the Example illustrations.
• Row Height Adjustments for Merged Cells, Automatic", Jim Rech, programming, 1999/04/02
• Text joining, separation, and reformatting with adjacent cells content, SepTerm separates words after 1st word into cell to the right.  SepLastTerm separates the last word into the cell to the right.  Not to be confused with merging and unmerging cell borders (containers).
• Sorting merged cells: group:*excel* sort merged cells
• Wrapping, not working with Merged Cells, AutoFitMergedCellRowHeight macro, Jim Rech, reposted 2002-08-09.  Problems with automatic adjustment of row height involving cells with wrapped text when there are merged cells present, originally posted 1999-04-02. 2003-10-01

---

This page was introduced on May 31, 2002. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP] 

Please send your comments concerning this web page to: David McRitchie send email comments

---

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved