Instead of hiding unused rows why not just delete them? it'll speed up your
process. there's a macro that will do it for you, type in "delete unused
rows" and I think you should get a hit on this site. If not I can always
post it here, it's rather short.

Another thing is when you're dumping data into the excel sheets are there
any formulai and if so, does it take a long time to recalculate these
formulai? On one sheet where I had a macro take a minute and a half I found
that recalculation took 30 seconds, so I was able to trim a third of the run
time off just because of that.

Last thing, when you say resetting a print area, you don't have your page
margins showing in excel do you? this alone takes up time and I have seen
numerous posts where it was the last thing checked and was the actual problem.

Just some more ideas,
Cheers,
Scott

"Gesualdo" wrote:

That's very helpful - I have suspected the spreadsheets themselves of slowing
things down - for example, I 've found that resetting a large print area
takes longer each time you do it. Again, the macro processes that most vary
in speed are ones that dump data into a spreadsheet from a series of pasted
arrays. I've also found (I think) that hiding unused rows slows things down,
at least in the sort of thing I'm doing. Once again thanks for the help Scott.

Philip

"Scott" wrote:

Just some things that I can think of that may help regarding the speed issue.
First to ensure it's as fast as possible try keeping the calculation command
as manual and screen updating as false. If you don't have these, it might
help a bit.

As for variability, does it slow down when you rerun the macro? or during a
one single run the computer is working harder then slowing down?

In case it's because of running the process several times, check and make
sure that you don't have any blank rows that excel might be including in the
data (control-end to check the last row). This could increase the memory
used by excel by quite a bit from one run to another.

If it is a memory issue go to an empty cell and type this in """"
=INFO("memused")/1024/1024 """" don't include the """". Anyways this will
give you the amount of memory that is being used in Mb. If you find that the
excel file gets bigger and bigger then you have at least found out your
problem is a memory issue and not a bottleneck.

cheers,
Scott


"Gesualdo" wrote:

That's an interesting aspect of dual core processors - worth looking into,
and thanks. However, I'm still baffled by the variability of the speed.

Philip

"Scott" wrote:

I'm not 100% sure but since you have a dual core processor I think the reason
why you don't get above 54% use is because of the two processors. i don't
think Excel 2003 has hyperthreading technology which would allow for both
processors to be used at the same time. So, next time you try running it,
check your CPU usage history. If one side is up near 100 and it's only
giving you 54% then it's because the other processor is doing little but
background stuff. I just tried running a test on my computer, dual processor
as well, and unless I'm running several programs at the same time I can see
that one processor is working much harder than the other.

Just a thing to check up on.

Cheers,
Scott

"Gesualdo" wrote:

I have a system with a complex Excel interface, running under VBA, storing
lots of data in Access via ADO. Some processes take several minutes to
complete, but the actual speed varies a lot, and at the moment things are
running very slowly. I'm using Office 2003, on a 1.86Ghz Core Duo machine
with 1Gb RAM. The processor doesn't run at more than 54% speed, suggesting
there's some form of bottleneck somewhere, but it's the variable nature of
the problem that's baffling. Sometimes I can get it to speed up, but it then
settles down to a slow speed again. Any ideas?