K
KMH
I have a spreadsheet that is extremely slow in Excel 2003 versus Excel 2002.
I think the problem is related to a change in Excel 2003 where subtotals
require a recalculation when rows are hidden or unhidden. We do use Offset
functions fairly extensively to do several different things that are also
partially to blame, but it doesn't slow it down in 2002. I could rewrite
some of the functionality I use the Offset function for into a VB macro, but
I would prefer not to have to do that if I can help it.
At any rate, we use 5 levels of row groups throughout this spreadsheet
because otherwise it is impossible to navigate 2000+ rows long so I don't
want to get rid of the row groups. I am open to suggestions, but the options
I came up with are....
1.Is there an event related to opening or closing row or column groups?
where I could shut off the recalc first.
2. Is there a way to turn off this new feature in Excel of recalculating
subtotals based upon visible rows?
Does anyone know how to do either of the above or another way to fix this
issue?
Thanks a lot for any help,
Ken
This link is where I got my information about Excel 2003 recalcs is below....
http://www.decisionmodels.com/calcsecretsi.htm...
Hiding or Unhiding Rows in Excel 2003
In Excel 2003 hiding or unhding rows will flag the selected rows as
uncalculated, even if no rows were actually hidden or unhidden. If
calculation is automatic this will trigger a recalculation. This behaviour is
a change from previous versions. Hiding or unhiding columns does NOT flag the
column as uncalculated.
Probably the reason for the change is that the SUBTOTAL function in Excel
2003 has an option to include or ignore hidden rows, so Excel needs to be
able to trigger a dependency recalculation by dirtying the cells when a row
is hidden or unhidden.
See evaluation circumstances for the circumstances that will cause a formula
to be evaluated.
I think the problem is related to a change in Excel 2003 where subtotals
require a recalculation when rows are hidden or unhidden. We do use Offset
functions fairly extensively to do several different things that are also
partially to blame, but it doesn't slow it down in 2002. I could rewrite
some of the functionality I use the Offset function for into a VB macro, but
I would prefer not to have to do that if I can help it.
At any rate, we use 5 levels of row groups throughout this spreadsheet
because otherwise it is impossible to navigate 2000+ rows long so I don't
want to get rid of the row groups. I am open to suggestions, but the options
I came up with are....
1.Is there an event related to opening or closing row or column groups?
where I could shut off the recalc first.
2. Is there a way to turn off this new feature in Excel of recalculating
subtotals based upon visible rows?
Does anyone know how to do either of the above or another way to fix this
issue?
Thanks a lot for any help,
Ken
This link is where I got my information about Excel 2003 recalcs is below....
http://www.decisionmodels.com/calcsecretsi.htm...
Hiding or Unhiding Rows in Excel 2003
In Excel 2003 hiding or unhding rows will flag the selected rows as
uncalculated, even if no rows were actually hidden or unhidden. If
calculation is automatic this will trigger a recalculation. This behaviour is
a change from previous versions. Hiding or unhiding columns does NOT flag the
column as uncalculated.
Probably the reason for the change is that the SUBTOTAL function in Excel
2003 has an option to include or ignore hidden rows, so Excel needs to be
able to trigger a dependency recalculation by dirtying the cells when a row
is hidden or unhidden.
See evaluation circumstances for the circumstances that will cause a formula
to be evaluated.