How to merge cells in excel using vb net

Answer

To merge cells in Excel using VB.NET, automate Excel with Microsoft Office Interop, and call the Range.Merge method on the target range. The merged range keeps the value from the upper-left cell of the range.

1. Add a reference to Microsoft.Office.Interop.Excel in your VB.NET project (NuGet or Add Reference → COM → Microsoft Excel Object Library). 

2. Use Excel.Application, open or create a workbook, get a worksheet, select a Range, then call Range.Merge() (or Range.Merge(True) to merge each row individually). 

Example (VB.NET, COM Interop — Windows)

Imports Excel = Microsoft.Office.Interop.Excel

Module MergeExample

    Sub Main()

        Dim excelApp As New Excel.Application()

        excelApp.Visible = True

        Dim wb As Excel.Workbook = excelApp.Workbooks.Add()

        Dim ws As Excel.Worksheet = CType(wb.Sheets(1), Excel.Worksheet)

        ' Put value in upper-left cell before merging (only this value will be preserved)

        ws.Range("B2").Value = "Merged title"

        ' Merge B2:D2 into a single cell

        Dim rng As Excel.Range = ws.Range("B2", "D2")

        rng.Merge()

        ' Optional: center horizontally and vertically

        rng.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter

        rng.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter

        ' Save and cleanup when finished

        wb.SaveAs("C:\Temp\MergedExample.xlsx")

        wb.Close()

        excelApp.Quit()

    End Sub

End Module

What happens to data when I merge cells?

Data from the upper-left cell of the range is retained; data in all other cells in the range is removed when merging. Preserve any other cell values by copying them elsewhere before merging.

How do I merge each row separately across columns?

To merge each row of a multi-row range separately, call Range.MergeAcross(True) or use the optional Across parameter for Range.Merge. The method will merge cells row by row rather than creating a single large merged block across rows.

Code snippet

' Merge each row in A2:C4 into three separate merged cells (A2:C2, A3:C3, A4:C4)
ws.Range("A2", "C4").MergeAcross(True)

What if I need to merge cells without Excel installed, or across platforms?

Use a library that manipulates the file format directly so Excel need not be installed (works on servers and non-Windows platforms). Recommended options:

1. Open XML SDK (DocumentFormat.OpenXml) — edits XLSX packages directly, cross-platform. 

2. Third-party libraries that have simple APIs: GemBox.Spreadsheet, Aspose.Cells, Spire.XLS, EPPlus, IronXL. GemBox and Aspose provide direct Merge APIs for .NET and VB.NET.

GemBox VB.NET example (creates merged cells without Excel)

Imports GemBox.Spreadsheet

Module GemBoxMerge

    Sub Main()

        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY") ' Replace with license if available

        Dim workbook = New ExcelFile()

        Dim ws = workbook.Worksheets.Add("Sheet1")

        ' Put value in top-left of target merged area

        ws.Cells("A1").Value = "Merged (GemBox)"

        ' Merge A1:C1

        ws.Cells.GetSubrange("A1", "C1").Merged = True

        workbook.Save("MergedWithGemBox.xlsx")

    End Sub

End Module

Use these libraries for reliable server-side or cross-platform processing and when COM automation is not an option. 

What is different on Windows vs Mac?

1. Windows: COM Automation (Microsoft.Office.Interop.Excel) works and is common for desktop apps that control a local Excel installation. It requires Excel to be installed on the same Windows machine. 

2. Mac: COM Interop is not supported on macOS. VB.NET apps targeting macOS cannot use Office COM automation to control Excel for Mac. Use file-format libraries (Open XML SDK, GemBox, Aspose, EPPlus, IronXL) for cross-platform merging. The Open XML approach works on macOS because it manipulates the XLSX package directly. 

How do I unmerge cells using VB.NET?

To unmerge a merged range with Interop, call.UnMerge(). For file-format libraries, use the library’s Merged = False or equivalent API. 

Interop example

Dim mergedRng As Excel.Range = ws.Range("B2", "D2")

If mergedRng.MergeCells Then

    mergedRng.UnMerge()

End If

1. Best practice: Put the value you want preserved into the upper-left cell before merging. 

2. Pitfall: Avoid merging cells in tables or where you must sort and filter frequently; merged cells break many Excel operations. 

3. Server-side: Do not use Office COM Interop on servers; use Open XML or a server-safe library instead.Cross-platform: Use Open XML SDK or third-party libraries for macOS, Linux, or cloud-hosted apps.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report