recalulating when hiding rows

  • Thread starter Thread starter ctsprowler
  • Start date Start date
C

ctsprowler

Hi,
I have a spreadsheet that uses both live data feeds and several excel
functions. This spreadsheet has a macro that hides/unhides certain
rows. As an example the VBA code used to do this is:

Rows("13:57").EntireRow.Hidden = True

The problem is that with Excel 2003 everytime I do this the extire
spreadsheet recalculates. This is slowing me down tremendously. I
added code to switch the spreadsheet to manual calculation, then
hide/unhide, and then switching the spreadsheet back to automatic
calculation mode. The code is given below:

Application.Calculation = xlCalculationManual
Application.Calculation = xlCalculationAutomatic

This seemed to help a bit in that fewer things seemed to be
recalculating. I read another post with a similar problem who used
the above solution and it seemed to work. He also mentioned that
Excel 2003 evaluates spreadsheet formulas differently than Excel 2002.
Does anyone have any ideas as to how to completely stop the
spreadsheet from updating when I hide/unhide rows?

Thanks for any help.
 
Not sure if it will help without more info but you can stop the
display from changing when processing the macro

Application.screenupdating = false

and switch back on at the end of the macro

Application.screenupdating = true

I put this in most macros, and it ususlly speeds things up

DavidP
 
Hi,
Hi, what are you doing with the un hidden rows? Checking whether rows
are unhidden, or using union and other set functions to create ranges
slows things a lot. It might be possible to work around this if you
give the details.
regards
Paul
 
Back
Top