Toggle Merge/UnMerge cells Excel Addin VBA, C#, VB.Net

Merge and UnMerge Methods

Merging contents of a cell into multiple cells to adjust complete view or share multiple figures to a common column in Excel. Unmerge is a reverse action which performs unmerge operation over merged cells. Microsoft offers two methods Merge and UnMerge underlying Range object which can be used to toggle cell contents based on need.

Prerequisites:

• Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
• Create Excel Addin in C# cor VB.Net code style
• Create a ribbon designer and put button

Input Source: following data we are using to test our code over, where we have a string written in Cell “A1” which we would like to merge between “A1:B1” cells:

VBA code example

Public Sub MergeAreaExample()
    'Declare range object
    Dim oRange As Range
    
    'Bind selection to Range
    Set oRange = Selection
    
    'Check merge cells
    If oRange.MergeCells = True Then
        'Unmerge cells
        oRange.Cells.UnMerge
    Else
        'Merge cells
        oRange.Cells.Merge
    End If
    
    'Memory cleanup
    Set oRange = Nothing
End Sub

C# code example

private void btnMergeCell_Click(object sender, RibbonControlEventArgs e)
{
	//Capture user selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;
	if(!oRange.MergeCells)
		oRange.Cells.Merge();
	else
		oRange.Cells.UnMerge();
}

VB.Net code example

Private Sub btnMergeUnMerge_Click(sender As Object, e As RibbonControlEventArgs) Handles btnMergeUnMerge.Click
	'Retain selection
	Dim oRange as excel.Range
	oRange = Globals.ThisAddIn.Application.Selection

	'Check merge cells in selection
	If oRange.MergeCells=True then
		'Unmerge cells
		oRange.Cells.UnMerge()
	Else 
		'Merge cells
		oRange.Cells.Merge()
	End If
End Sub

Explanation:

The first line of code is capturing the selection made by user into a Range object then we are using MergeCells property to determine if cell is merged or not. It is a boolean property which returns true or false. If code finds cells are already merged it will trigger else block and cells gets Unmerged. If MergeCells returns false the If block will be triggered and Merge operation will occur within selection and content would be adjusted accordingly as shown in below examples:

Example Merge contents between column “A1:B1”

The output is not showing complete text hence we have to do Text Wrap here to see complete string as follow:

Let’s do Unmerge and see what happens:

Finally we got the unmerged operation accomplished and back to initial stage.

Next: Number Formats in Excel using VSTO C#

Share on Facebook

Tweet