Used range waste % and dynamic arrays

[Carlos]

Hello, I am starting to use dynamic arrays and when I check the workbook in one of the sheets with extensive use of dynamic arrays of hundreds of rows I am having a huge waste percentage. I have cleaned the workbook and when I type Ctrl-End it goes correctly to the end of the sheet (spill area).

After some testing I am thinking that for the calculation of the waste space FastExcel is not taking in consideration the used spill areas of the dynamic arrays and is counting them as waste space.

I would appreciate a confirmation of this behavior.

Thank you!

[Charles Williams]

Hi Carlos,

Yes: FastExcel reports the used range as containing the spill range, but because the spill cells do not actually contain anything FXL counts the spill cells as waste.

This FXL behaviour is technically correct but probably misleading.

But the way Excel handles used range with spilling arrays is tricky. If you expand a spill range the used range expands to match. But if you then contract the spill range the used range does not actually contract until you press Ctrl-End (or use Clean Workbook)

I think what FXL probably should be doing is not counting the spill cells as waste

[Carlos]

After some time I realized of this.  I agree it would avoid the misunderstanding if it would not count spill areas as waste.

I asume dynamic arrays are not being used a lot yet, but I am liking them a lot to have cleaner workbooks.