Cannot detect merged cells through Range.MergeCells Property for Excel

Question

When I send data to Excel it ignores the merged "property" of some cells and just writes to the first cell it finds. So assuming I have column A and column B merged and I am sending data to column A and C, it actually splits the merged column so I am left with an empty column B.

Here is some code for context (some variables have been kept generic):

Range cells = this.Worksheet.Cells;
Range cell = (Range)cells[rowIndex, columnIndex];
Boolean merged = (Boolean)cell.MergeCells;        //Here I am trying to determine if the
                                                  //cell is merged.

My problem is that .MergeCells always returns false. What am I doing wrong here? I know that in the Excel worksheet the cells are merged.

Have you tried it without casting the result of .MergeCells directly to a Boolean? [This MSDN page][1] says that .MergeCells returns an object. It probably wouldn't matter, but the method is pretty straightfoward; so unless you are not using the correct Range then maybe that could be it? [1]: msdn.microsoft.com/en-us/library/… — rwisch45

Colm Bhandal Colm Bhandal · Accepted Answer · 2020-03-24T14:51:55

The problem is you are casting to a boolean, and MergeCells is not always guaranteed to give you back a boolean, as outlined in this more recent question: how to detect merged cells in c# using MS interop excel. You need to also check for the value of null - see the linked question for how to do that.

Hypothesis

So what's probably happening to your code is the null value casts back to false, even though what the null value actually indicates is that there are merged cells in the range.

Cannot detect merged cells through Range.MergeCells Property for Excel

2 Answers

Hypothesis