Speeding Up A Spreadsheet
 

Hi all,

I've got a large(ish) spreadsheet with a fairly voluminous amount of
formulas. Due to this it can take quite a while to open and run various
macros that sift data depending on the user. This is fine with me, but a
number of my colleagues are complaining that it takes too long to get
into or sort (obviously never heard of patience being a virtue).

Aside from tinkering with the computer itself, can anyone suggest a
means of speeding up the spreadsheet?

TIA,

SamuelT


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=552653

Speeding Up A Spreadsheet
 

The simplest way is to turn the screen updating off. At the beginning of
your code put...


Application.screenupdating = False

They they might complain that they can't see anything happening. If
this is the case, true giving an occasional indication that something
is going on by setting the value of the status bar e.g.

Application.StatusBar = "Just started...."
...
...
...
...
Application.StatusBar = "Almost finished...."
...
End Sub


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=552653

Speeding Up A Spreadsheet
 

You might also try ridding your code of Select methods. If your macro
was recorded, it will be full of these.

For example..

Range("A1").Select
Selection.interior.colorIndex = 6

becomes

Range("A1").interior.colorIndex = 6


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=552653