CODEWITHSUNDEEP
c#excel
Tak
Tak

Reputation: 3616

"We can't do that to a merged cell" error in excel c#

I have an excel file found in this link so what I would want to do is copy row 22 (highlighted in red) with it's format and formulas then insert it to be row 23 then copy row 25 (highlighted in red) with it's format and formulas then insert it to be row 26. When debugging, it works for row 22 but then gives "We can't do that to a merged cell" error in this line sourceRange2.Copy(destRange2);

Here is my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using Microsoft.Office.Interop.Excel;
using System.IO;
using System.Reflection;
using System.Text;

namespace WindowsFormsApp1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private object missing = Missing.Value;
        private void button1_Click(object sender, EventArgs e)
        {
            
            Excel.Application myApp = new Excel.Application();
            Excel.Workbook my_workbook = myApp.Workbooks.Open(@"C:\Test\myexcel.xlsx");
            Excel.Worksheet my_ws = my_workbook.Worksheets[1];

            
            Range range = (Excel.Range)my_ws.Rows[22, this.missing];
            range.Insert(Excel.XlDirection.xlDown);
            //Source Range
            Range sourceRange = my_ws.Range["A22","H22"];  
            //Destination range
            Range destRange = my_ws.Range["A23", "H23"];
            sourceRange.Copy(destRange);

            Range range2 = (Excel.Range) my_ws.Rows[25, this.missing];
            range2.Insert(Excel.XlDirection.xlDown);
            //Source Range
            Range sourceRange2 = my_ws.Range["A25", "H25"];
            //Destination range
            Range destRange2 = my_ws.Range["A26", "H26"];
            sourceRange2.Copy(destRange2);

            my_workbook.Save();
            
        }
       
    }
}

Upvotes: 0

Views: 2301

Answers (1)

Kieran501
Kieran501

Reputation: 191

After you've added a row the first time, the second red line is no longer at row 25, its at row 26. Row 25 is now part of the vertically merged cells, hence the merged cell error. So the second part of your code should read

    Range range2 = (Excel.Range) my_ws.Rows[26, this.missing];
    range2.Insert(Excel.XlDirection.xlDown);
    //Source Range
    Range sourceRange2 = my_ws.Range["A26", "H26"];
    //Destination range
    Range destRange2 = my_ws.Range["A27", "H27"];
    sourceRange2.Copy(destRange2);

Also i recommend closing the excel file if your not doing so somewhere else, otherwise you end up with lots of instances of excel open

my_workbook.Close();

Upvotes: 1

Related Questions