Problem with MaxDataRow (when there are merged cells)

Hi,

There seems to be a problem in the way MaxDataRow computes its value if the worksheet contains cells in consecutive rows that have been merged. Please check out the attached .NET project.


Thanks,
Charlie Lotridge

Hi Charlie,

Here is my result:

A B C D

This is a test.
MaxDataRow=0
MaxDataRowInColumn(0)=5
MaxDataRowInColumn(1)=0
MaxDataRowInColumn(2)=0
MaxDataRowInColumn(3)=0

What's wrong with it. Since A1:D4 are merged cell into A1 cell, so, firstly Cells.MaxDataRow should be 0 which is right. Then, Cells.MaxDataRowInColumn(0)=5... it is also correct as when the line is processed, there would be an entry into A6 cell i.e. "MaxDataRow=0". For columns B, C and D the MaxDataRowInColumn will be 0 as there is no more data in those columns under the merged cell (A1:D4). For your info, MaxDataRow and MaxDataRowInColumn only returns the farthest row index (zero based).

Do you think some different results and have some contradiction, kindly let us know.

Thank you.

Hi Amjad,

Thanks for your quick response.

I mostly understand your answer, though I'm not quite clear on why the MaxDataRowInColumn(0) should be 5. For this you say "...there would be an entry into A6 cell i.e. "MaxDataRow=0"..." which I don't quite understand. Can you explain?

However, a couple points...

First, I understand that none of the underlying cells of A2 through D4 really have any data - as you mention the actual data really resides in cell A1. However, consider that MaxDataRow is likely to be used to effectively ask the question "on what row can I safely start adding new content without overlapping existing content?" This is how I was trying to use it anyway, but it turns out it's not providing the answer that question.

Second, it seems to me that MaxDataRow should be equal to the maximum value of MaxDataRowInColumn for all columns. So, these currently (in my opinion) lack consistency.

Regardless, I've implemented a workaround to get the value I need and is attached as MaxDataRow.txt.

Thanks,

Charlie

Hi,

I mostly understand your answer, though I'm not quite clear on why the MaxDataRowInColumn(0) should be 5. For this you say "...there would be an entry into A6 cell i.e. "MaxDataRow=0"..." which I don't quite understand. Can you explain?

Well to explain it, take a look at this part of your code:

int row = 5;

cells[ row++, 0 ].PutValue(String.Format("MaxDataRow={0}", cells.MaxDataRow));

cells[ row++, 0 ].PutValue(String.Format("MaxDataRowInColumn(0)={0}", cells.MaxDataRowInColumn(0)));

Now, the second line will print "MaxDataRow=0" into A6 cell.

But when the third line is processed, we have a new entry into A6 cell (first column). So, when this line is processed the maximum data row index in the first column should be 5 and not zero. Therefore this line will result as "MaxDataRowInColumn(0)=5". Hope, you got it now.

Thank you.