How to: Merge Cells or Split Merged Cells

Sep 19, 2023
5 minutes to read

This topic demonstrates how to merge a cell range into one cell and split merged cells.

Merge Cells

To merge multiple adjacent cells into one cell, call the worksheet’s Worksheet.MergeCells method and pass the cell range you want to merge. You can also use the range’s RangeExtensions.Merge method to merge cells in this range.

Both methods can accept a MergeCellsMode parameter that allows you to specify how to merge cells (ignore or include intersections with other merged cells, merge rows, or merge columns). See the MergeCellsMode enumeration description for additional information and examples.

Note

When you merge a cell range, the data (value, formula, and format settings) of the top left non-empty cell appears in the resulting merged cell. Other cell data is lost.

If all cells within the original range are empty, the range’s top left cell format is applied to the merged cell.

In the following code sample, the Worksheet.MergeCells method merges the “A1:C4” cell range and the RangeExtensions.Merge method merges the “A6:C8” cell range into one cell and merges each column in the “E1:G5” cell range:

View Example

C#
VB.NET

csharp

using DevExpress.Spreadsheet;
using System.Drawing;
// ...

// Create a new Workbook object.
using (Workbook workbook = new Workbook())
{
    // Access the first worksheet of the workbook.
    Worksheet worksheet = workbook.Worksheets[0];

    // Fill the "A2" cell with the LightGray color.
    worksheet.Cells["A2"].FillColor = Color.LightGray;

    // Specify the "B2" cell value. 
    worksheet.Cells["B2"].Value = "B2";
    // Fill the "B2" cell with the LightGreen color.
    worksheet.Cells["B2"].FillColor = Color.LightGreen;

    // Specify the "C3" cell value. 
    worksheet.Cells["C3"].Value = "C3";
    // Fill the "C3" cell with the LightSalmon color.
    worksheet.Cells["C3"].FillColor = Color.LightSalmon;

    // Specify the "A6" cell value.
    worksheet.Cells["A6"].Value = "A6";
    // Fill the "A6" cell with the LightBlue color.
    worksheet.Cells["A6"].FillColor = Color.LightBlue;

    // Specify the "E1" cell value.
    worksheet.Cells["E1"].Value = "E1";
    // Fill the "E1" cell with the LightCoral color.
    worksheet.Cells["E1"].FillColor = Color.LightCoral;

    // Specify the "F2" cell value.
    worksheet.Cells["F2"].Value = "F2";
    // Fill the "F2" cell with the LightCyan color.
    worksheet.Cells["F2"].FillColor = Color.LightCyan;

    // Specify the "G3" cell value.
    worksheet.Cells["G3"].Value = "G3";
    // Fill the "G3" cell with the LightYellow color.
    worksheet.Cells["G3"].FillColor = Color.LightYellow;

    // Merge the "A1:C4" cell range into one cell.
    worksheet.MergeCells(worksheet.Range["A1:C4"]);

    // Merge the "A6:C8" cell range into one cell.
    worksheet.Range["A6:C8"].Merge();

    // Merge the "E1:G5" cell range by columns.
    worksheet.Range["E1:G5"].Merge(MergeCellsMode.ByColumns);
}

Imports DevExpress.Spreadsheet
Imports System.Drawing
' ...

' Create a new Workbook object.
Using workbook As New Workbook()
    ' Access the first worksheet of the workbook.
    Dim worksheet As Worksheet = workbook.Worksheets(0)

    ' Fill the "A2" cell with the LightGray color.
    worksheet.Cells("A2").FillColor = Color.LightGray

    ' Specify the "B2" cell value. 
    worksheet.Cells("B2").Value = "B2"
    ' Fill the "B2" cell with the LightGreen color.
    worksheet.Cells("B2").FillColor = Color.LightGreen

    ' Specify the "C3" cell value. 
    worksheet.Cells("C3").Value = "C3"
    ' Fill the "C3" cell with the LightSalmon color.
    worksheet.Cells("C3").FillColor = Color.LightSalmon

    ' Specify the "A6" cell value.
    worksheet.Cells("A6").Value = "A6"
    ' Fill the "A6" cell with the LightBlue color.
    worksheet.Cells("A6").FillColor = Color.LightBlue

    ' Specify the "E1" cell value.
    worksheet.Cells("E1").Value = "E1"
    ' Fill the "E1" cell with the LightCoral color.
    worksheet.Cells("E1").FillColor = Color.LightCoral

    ' Specify the "F2" cell value.
    worksheet.Cells("F2").Value = "F2"
    ' Fill the "F2" cell with the LightCyan color.
    worksheet.Cells("F2").FillColor = Color.LightCyan

    ' Specify the "G3" cell value.
    worksheet.Cells("G3").Value = "G3"
    ' Fill the "G3" cell with the LightYellow color.
    worksheet.Cells("G3").FillColor = Color.LightYellow

    ' Merge the "A1:C4" cell range into one cell.
    worksheet.MergeCells(worksheet.Range("A1:C4"))

    ' Merge the "A6:C8" cell range into one cell.
    worksheet.Range("A6:C8").Merge()

    ' Merge the "E1:G5" cell range by columns.
    worksheet.Range("E1:G5").Merge(MergeCellsMode.ByColumns)
End Using

Split Merged Cells

Call the worksheet’s Worksheet.UnMergeCells method and pass the range that contains the merged cells. You can also use the range’s RangeExtensions.UnMerge method to split merged cells in this range.

The following code sample splits cells merged in the previous section:

C#
VB.NET

csharp

using DevExpress.Spreadsheet;
// ...

// Create a new Workbook object.
using (Workbook workbook = new Workbook())
{
    // Access the first worksheet of the workbook.
    Worksheet worksheet = workbook.Worksheets[0];
    //...

    // Split merged cells in the "A1:C4" cell range.
    worksheet.UnMergeCells(worksheet.Range["A1:C4"]);

    // Split merged cells in the "A6:C8" cell range.
    worksheet.Range["A6:C8"].UnMerge();

    // Split merged cells in the "E1:G5" cell range.
    worksheet.Range["E1:G5"].UnMerge();
}

Imports DevExpress.Spreadsheet
' ...

' Create a new Workbook object.
Using workbook As New Workbook()
    ' Access the first worksheet of the workbook.
    Dim worksheet As Worksheet = workbook.Worksheets(0)
    '...

    ' Split merged cells in the "A1:C4" cell range.
    worksheet.UnMergeCells(worksheet.Range("A1:C4"))

    ' Split merged cells in the "A6:C8" cell range.
    worksheet.Range("A6:C8").UnMerge()

    ' Split merged cells in the "E1:G5" cell range.
    worksheet.Range("E1:G5").UnMerge()
End Using

Split All Merged Cells in a Worksheet

Call the CellRange.GetMergedRanges method to obtain all merged ranges that intersect a specific area.

The following code sample finds and splits all merged cell ranges in a worksheet:

C#
VB.NET

csharp

using DevExpress.Spreadsheet;
// ...

// Create a new Workbook object.
using (Workbook workbook = new Workbook())
{
    // Access the first worksheet of the workbook.
    Worksheet worksheet = workbook.Worksheets[0];
    //...

    // Split all merged cells in the worksheet.
    foreach (var item in worksheet.Cells.GetMergedRanges()) {
        item.UnMerge();
    }
}

Imports DevExpress.Spreadsheet
' ...

' Create a new Workbook object.
Using workbook As New Workbook()
    ' Access the first worksheet of the workbook.
    Dim worksheet As Worksheet = workbook.Worksheets(0)
    '...

    ' Split all merged cells in the worksheet.
    For Each item In worksheet.Cells.GetMergedRanges()
        item.UnMerge()
    Next item
End Using