I have a sub routine that runs though a series of workbooks, and through
each worksheet within each workbook. The routine performs it's purpose and
determines whether a row needs to be deleted.

If a condition is met then the flowing line of code performs the deletion:

[ForecastStart].Offset(r - 1, 0).EntireRow.Delete xlShiftUp

When the code runs the various workbooks are opening, the worksheets looped
through and saved and closed again.

PROBLEM

The code starts of running very quickly but slowly slows to a crawl. The
line of code above is the one which causes the problem. When I step through
the code it is this line that takes longer and longer to execute as the
length of time the program has been running lengthens.

The order I loop through the workbooks makes no difference. If I loop in a
different order the workbooks, with the apparently slow workbooks at the
front then they whizz through and the fast ones now crawl.

Both screenupdating has been set to false and calculation set to manual to
try to alleviate the problem.

Please please someone give me some things to try!

Paul,

When accessing PageSetUp or deleting columns / rows use...

"YourSheet".DisplayPageBreaks = False

immediately after the operation.
You can get an amazing speed increase, as Excel does not have to figure out
where to put the pagebreaks. I have had best results using it before AND
after.

Regards,
Jim Cone
San Francisco,CA
***************
"Paul W Smith" wrote in message
...
I have a sub routine that runs though a series of workbooks, and through
each worksheet within each workbook. The routine performs it's purpose and
determines whether a row needs to be deleted.
If a condition is met then the flowing line of code performs the deletion:
[ForecastStart].Offset(r - 1, 0).EntireRow.Delete xlShiftUp
When the code runs the various workbooks are opening, the worksheets
looped
through and saved and closed again.
PROBLEM
The code starts of running very quickly but slowly slows to a crawl. The
line of code above is the one which causes the problem. When I step through
the code it is this line that takes longer and longer to execute as the
length of time the program has been running lengthens.
The order I loop through the workbooks makes no difference. If I loop in
a different order the workbooks, with the apparently slow workbooks at the
front then they whizz through and the fast ones now crawl. Both
screenupdating has been set to false and calculation set to manual to try to
alleviate the problem.
Please please someone give me some things to try!

Jim,

Thanks for your suggestion, I put your line of code either side of my line
deletion statement, but on this occasion it does not seem to be making any
discernable difference to my problem.

Any others ideas anyone?

Paul Smith





"Jim Cone" wrote in message
...
Paul,

When accessing PageSetUp or deleting columns / rows use...

"YourSheet".DisplayPageBreaks = False

immediately after the operation.
You can get an amazing speed increase, as Excel does not have to figure
out
where to put the pagebreaks. I have had best results using it before AND
after.

Regards,
Jim Cone
San Francisco,CA
***************
"Paul W Smith" wrote in message
...
I have a sub routine that runs though a series of workbooks, and through
each worksheet within each workbook. The routine performs it's purpose
and
determines whether a row needs to be deleted.
If a condition is met then the flowing line of code performs the
deletion:
[ForecastStart].Offset(r - 1, 0).EntireRow.Delete xlShiftUp
When the code runs the various workbooks are opening, the worksheets
looped
through and saved and closed again.
PROBLEM
The code starts of running very quickly but slowly slows to a crawl.
The
line of code above is the one which causes the problem. When I step
through
the code it is this line that takes longer and longer to execute as the
length of time the program has been running lengthens.
The order I loop through the workbooks makes no difference. If I loop
in
a different order the workbooks, with the apparently slow workbooks at the
front then they whizz through and the fast ones now crawl. Both
screenupdating has been set to false and calculation set to manual to try
to
alleviate the problem.
Please please someone give me some things to try!

Paul,

OK then, are you deleting from the bottom up?...
Something like...

For MyRow = LastRow to FirstRow Step -1
If .... Then Rows(MyRow).Delete
Next 'MyRow

Regards,
Jim Cone
San Francisco, CA
***************
"Paul W Smith" wrote in message
...
Jim,
Thanks for your suggestion, I put your line of code either side of my line
deletion statement, but on this occasion it does not seem to be making any
discernable difference to my problem.
Any others ideas anyone?
Paul Smith

"Jim Cone" wrote in message
...
Paul,
When accessing PageSetUp or deleting columns / rows use...
"YourSheet".DisplayPageBreaks = False
immediately after the operation.
You can get an amazing speed increase, as Excel does not have to figure
out where to put the pagebreaks. I have had best results using it before
AND after.
Regards,
Jim Cone
San Francisco,CA
***************
"Paul W Smith" wrote in message
...
I have a sub routine that runs though a series of workbooks, and
through
each worksheet within each workbook. The routine performs it's purpose
and
determines whether a row needs to be deleted.
If a condition is met then the flowing line of code performs the
deletion:
[ForecastStart].Offset(r - 1, 0).EntireRow.Delete xlShiftUp
When the code runs the various workbooks are opening, the worksheets
looped
through and saved and closed again.
PROBLEM
The code starts of running very quickly but slowly slows to a crawl.
The line of code above is the one which causes the problem. When I step
through
the code it is this line that takes longer and longer to execute as the
length of time the program has been running lengthens.
The order I loop through the workbooks makes no difference.
If I loop in a different order the workbooks, with the apparently
slow workbooks at the front then they whizz through and the fast ones now
crawl. Both screenupdating has been set to false and calculation set to
manual to try to alleviate the problem.
Please please someone give me some things to try!

Yes... is there any other way of doing it?


"Jim Cone" wrote in message
...
Paul,

OK then, are you deleting from the bottom up?...
Something like...

For MyRow = LastRow to FirstRow Step -1
If .... Then Rows(MyRow).Delete
Next 'MyRow

Regards,
Jim Cone
San Francisco, CA
***************
"Paul W Smith" wrote in message
...
Jim,
Thanks for your suggestion, I put your line of code either side of my
line
deletion statement, but on this occasion it does not seem to be making
any
discernable difference to my problem.
Any others ideas anyone?
Paul Smith

"Jim Cone" wrote in message
...
Paul,
When accessing PageSetUp or deleting columns / rows use...
"YourSheet".DisplayPageBreaks = False
immediately after the operation.
You can get an amazing speed increase, as Excel does not have to
figure
out where to put the pagebreaks. I have had best results using it before
AND after.
Regards,
Jim Cone
San Francisco,CA
***************
"Paul W Smith" wrote in message
...
I have a sub routine that runs though a series of workbooks, and
through
each worksheet within each workbook. The routine performs it's
purpose
and
determines whether a row needs to be deleted.
If a condition is met then the flowing line of code performs the
deletion:
[ForecastStart].Offset(r - 1, 0).EntireRow.Delete xlShiftUp
When the code runs the various workbooks are opening, the worksheets
looped
through and saved and closed again.
PROBLEM
The code starts of running very quickly but slowly slows to a crawl.
The line of code above is the one which causes the problem. When I step
through
the code it is this line that takes longer and longer to execute as
the
length of time the program has been running lengthens.
The order I loop through the workbooks makes no difference.
If I loop in a different order the workbooks, with the apparently
slow workbooks at the front then they whizz through and the fast ones now
crawl. Both screenupdating has been set to false and calculation set to
manual to try to alleviate the problem.
Please please someone give me some things to try!

Here are some other ways of deleting rows.

http://www.rubbershoe.com/deleterows.htm



"Paul W Smith" wrote in message
...
Yes... is there any other way of doing it?


"Jim Cone" wrote in message
...
Paul,

OK then, are you deleting from the bottom up?...
Something like...

For MyRow = LastRow to FirstRow Step -1
If .... Then Rows(MyRow).Delete
Next 'MyRow

Regards,
Jim Cone
San Francisco, CA
***************
"Paul W Smith" wrote in message
...
Jim,
Thanks for your suggestion, I put your line of code either side of my
line
deletion statement, but on this occasion it does not seem to be making
any
discernable difference to my problem.
Any others ideas anyone?
Paul Smith

"Jim Cone" wrote in message
...
Paul,
When accessing PageSetUp or deleting columns / rows use...
"YourSheet".DisplayPageBreaks = False
immediately after the operation.
You can get an amazing speed increase, as Excel does not have to
figure
out where to put the pagebreaks. I have had best results using it
before
AND after.
Regards,
Jim Cone
San Francisco,CA
***************
"Paul W Smith" wrote in message
...
I have a sub routine that runs though a series of workbooks, and
through
each worksheet within each workbook. The routine performs it's
purpose
and
determines whether a row needs to be deleted.
If a condition is met then the flowing line of code performs the
deletion:
[ForecastStart].Offset(r - 1, 0).EntireRow.Delete xlShiftUp
When the code runs the various workbooks are opening, the
worksheets
looped
through and saved and closed again.
PROBLEM
The code starts of running very quickly but slowly slows to a
crawl.
The line of code above is the one which causes the problem. When I step
through
the code it is this line that takes longer and longer to execute as
the
length of time the program has been running lengthens.
The order I loop through the workbooks makes no difference.
If I loop in a different order the workbooks, with the apparently
slow workbooks at the front then they whizz through and the fast ones
now
crawl. Both screenupdating has been set to false and calculation set
to
manual to try to alleviate the problem.
Please please someone give me some things to try!

From a Tech Support standpoint when my users complain that any application is suddenly slowed down, the first thing I do is delete all of the tmp files on the machine. When it comes to Excel, their complaint is generally that EVERY Excel file opens and responds slowly. Even though you indicated that you're running a subroutine and experiencing this in a particular workbook, it still may be worth it to delete all of the .tmp files from the machine. I've seen on at least 5 seperate occasions where the .tmp's had brought Excel to a crawl and once I deleted those, the problem cleared up immediately.
Assumimg it's not related to your code, it could also be memory related in the sense that after the routine runs for so long, it begins accessing an area of RAM that's bad (I've had this happen with other software.) Likewise, do you have enough memory
To rule memory out, run the subroutine on a different PC if possible. Although I seriously doubt it's memory related, it may be worth trying unless you're convinced that it's somewhere in your code

Tony

?B?VG9ueQ==?= wrote

Hi,
This is off-topic, but is there any way in your newsreader to set a wrap
text column? I read your posts with interest, but often have to scroll
right to see it all.

--
David

Tony,

Thank you for taking the time to offer your comments.

I have run the process on an old and much slower machine with only 256Mb of
RAM, while the machine with the problem apparently has 512Mb. On the older
machine the code operates without a problem, so it looks like I have a
problem with my RAM, so your advice was invaluable.

My next question is what do I do to solve this problem? Can you offer any
further assistance? Do I need to take my PC in for repair?

Paul Smith


"Tony" wrote in message
...
From a Tech Support standpoint when my users complain that any application
is suddenly slowed down, the first thing I do is delete all of the tmp files
on the machine. When it comes to Excel, their complaint is generally that
EVERY Excel file opens and responds slowly. Even though you indicated that
you're running a subroutine and experiencing this in a particular workbook,
it still may be worth it to delete all of the .tmp files from the machine.
I've seen on at least 5 seperate occasions where the .tmp's had brought
Excel to a crawl and once I deleted those, the problem cleared up
immediately.
Assumimg it's not related to your code, it could also be memory related in
the sense that after the routine runs for so long, it begins accessing an
area of RAM that's bad (I've had this happen with other software.) Likewise,
do you have enough memory?
To rule memory out, run the subroutine on a different PC if possible.
Although I seriously doubt it's memory related, it may be worth trying
unless you're convinced that it's somewhere in your code.

Tony