Merge and Unmerge Cells

Location: http://www.mvps.org/dmcritchie/excel/merge.htm      
     Code: http://www.mvps.org/dmcritchie/excel/code/merge.txt
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]
Copyright (C)1997 - 2002   F.  David McRitchie

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
 ABCDEF
1A1-1 B1-1C1-1 D1-1E1-1 F1-1
2A2B2C2D2 E2F2
3A3B3C3D3 E3F3
4A4-2 B4-2C4-3 D4-3E4-4 F4-4
5A5B5C5D5 E5F5
6A6B6-5 C6-5D6-6 E6-7F6-7
7A7B7C7 D7-6E7-7 F7-7
8A8-8 B8-8C8D8-6 E8F8
9A9-8 B9-8C9D9-6 E9-9F9-9
10A10-8 B10-8C10D10-6 E10-9F10-9
 
 ABCDEF
1A1-1
2A2B2C2D2 E2F2
3A3B3C3D3 E3F3
4A4-2 C4-3 E4-4
5A5B5C5D5 E5F5
6A6 B6-5D6-6 E6-7
7A7B7C7 D7-6E7-7
8A8-8 C8D8-6E8F8
9A9-8 C9D9-6 E9-9
10A10-8 C10D10-6 E10-9
 
 ABCDEF
1A1-1
2A2B2C2D2 E2F2
3A3B3C3D3 E3F3
4A4-2 C4-3 E4-4
5A5B5C5D5 E5F5
6A6 B6-5 D6-6 E6-7
7A7B7C7
8 A8-8C8E8 F8
9C9 E9-9
10C10

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
A1B1C1D1
 B2C2D2
 B3C3D3
 B4C4D4
A5B5C5D5
 B6C6D6
 B7C7D7
 B8C8D8
 B9C9D9
A10B10C10D10
 B11C11D11
 B12C12D12
 
A1B1C1D1
B2C2D2
B3C3D3
B4C4D4
A5B5C5D5
B6C6D6
B7C7D7
B8C8D8
B9C9D9
A10B10C10D10
B11C11D11
B12C12D12

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)

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.

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

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