row hide triggers recalculation

[lalbatros]

I can't understand and I can't find a solution to this one.

When I hide one or several columns in a sheet, nothing is recalculated.
However, when I do the same for row(s), then the spreadsheet is recalculated.
If I simply resize the height to a small value, then nothing happens.
Of course, if I resize to a height of 0 px, then it recalculates again.
This behaviour is the same in macros or when performed manually.

I did many tests, like removing columns of formulas, the behaviour remains.
I would like to avoid this recalculations since it takes several seconds and because it is very useful in my application to show and hide parts of the spreadsheet. (the long calculation occupies only one cell)

Thanks for your hints.

 

[DonkeyOte]

taken from

http://msdn.microsoft.com/en-us/library/aa730921.aspx

Volatile Actions

Volatile actions are actions that trigger a recalculation. These include the following:

*

Clicking a row or column divider when you are in automatic mode.
*

Inserting or deleting rows, columns, or cells anywhere on a sheet.
*

Adding, changing, or deleting defined names.
*

Renaming worksheets or changing worksheet position when you are in automatic mode.
*

Filtering, hiding, or un-hiding rows in Excel 2003 or Excel 2007.
*

Opening a workbook when you are in automatic mode. If the workbook was last calculated by a different version of Excel, opening the workbook usually results in a full calculation.
*

Saving a workbook in manual mode if the Calculate before Save option is selected.

 

[lalbatros]

Now I understand why!

Filtering, hiding, or un-hiding rows in Excel 2003 or Excel 2007.

I guess this is a design choice related to lists, filters, groupings or consolidation.

But why do they recalculate when there are no such items on the sheet?
Design after hours?
And why breaking the simple calculation rules of spreadsheet?
For example, I think that the result of a calculation should not depend on the visibility of some elements.
There is a usual tendency of excel users to mix presentation goals and data storage.
But now it appears that the design of excel itself sometimes mixes view and data.
I mean that a calculation result may depend on the presentation.
Really bad!

But is there a way to solve my problem instead of telling everybody than I am angry?

 

[RoryA]

Excel 2003 and 2007 both extended the SUBTOTAL functions so that they could ignore manually hidden rows as well as filtered ones. In order to do that, I guess they had to have the hiding of rows trigger a Calculate event. Your best bet is probably to disable events, hide the rows and then re-enable events?

 

[lalbatros]

rorya,

I do this in my macro before hiding what I want to hide:

    xl.Calculation = xlCalculationManual
    xl.EnableEvents = False
    xl.ScreenUpdating = False

And I reverse this when finished.

This doesn't solve my problem.
My understanding is that disabling events just disables the response to the events, but the event stack continues to records everything as before. Re-enabling event just restarts the responses to the events.

In principle, I should try to avoid the change event feeding the event stack.
Or I should try to erase part or reset totally the event stack.
Is that possible?
Would that be reasonnable?

Thanks

 

[RoryA]

It would be reasonable, but not possible. In point of fact, I suspect the order is the other way around - the row hide triggers a recaculation which triggers the event. If it's just a display thing I would go with setting the row height very small.

 

[lalbatros]

you are right rorya,

I tested very small row height, and it works.
However, I don't think I will implement it, because restorin the row height is a nightmare.
The "AutoSize" method triggers a recalculation too ... ;>}