row hide triggers recalculation

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
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.
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

Well-known Member
Joined
Sep 5, 2007
Messages
659
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,534
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Sep 5, 2007
Messages
659

ADVERTISEMENT

rorya,

I do this in my macro before hiding what I want to hide:
Code:
    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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,534
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Sep 5, 2007
Messages
659
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 ... ;>}
 

Watch MrExcel Video

Forum statistics

Threads
1,128,125
Messages
5,628,844
Members
416,344
Latest member
Maug2004

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top