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!

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

Paul,
Sounds like you might have the classic limitation in Excel storage in the
TEMP directory, which has a significant impact on speed. First thing to do
is close Excel and delete all temp files (especially .emf).

Dave McRitchie has a really good treatment for everything performance
related at www.mvps.org/dmcritchie/excel/slowresp.htm. (Serach to Temporary
files on a very long web page). You should find some really intersting stuff
there.

(By the way, Dave also talks about not releasing objcets after use (Like Set
MyObject = Nothing). Could that be the source of your accumulating
slowdown?)

Another accumulating slowdown I have had to deal with is related to
iteratively adding sheets to the same workbook. Are you doing this somewhere
in the routine?

Someone (I think Debra Dalgliesh) posed a startup macro to clean the Temp
directory some time ago -
below is the copy I have used:

Hope this might get you in the right direction,
AlexJ

Private Sub DeleteEMFs()

Dim fso As Variant
On Error GoTo ERRfso
Set fso = CreateObject("Scripting.FileSystemObject")

Dim fs As FileSearch
Dim i As Long
Set fs = Application.FileSearch
With fs
.LookIn = fso.GetSpecialFolder(2)
.FileName = "*.emf"

If .Execute(SortBy:=msoSortByFileName,
SortOrder:=msoSortOrderAscending) 0 Then

On Error Resume Next
sBar ("Clearing .emf files from TEMP directory")
For i = 1 To .FoundFiles.Count
Kill .FoundFiles(i)
Next i
On Error GoTo 0
End If
sBar ("")
End With
Exit Sub
ERRfso:
Rtn = MsgBox("fso File Access Error - EMFs Cannot Be Accessed",
vbInformation + vbOKCancel, "[INIT0 - DeleteEMFs]")
Set fso = Nothing
Set fs = Nothing
End Sub





"Paul W Smith" wrote in message
...
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

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!

Paul

I would not take the PC in for repair at this point as the problem is not yet isolated. It still could be the memory, but I'm more inclined to go with the explanation offered by Alex and would exhaust his suggestions first. One thing you can do is swap the memory (assuming it's the same type of RAM in both machines that you ran the code on) in the machines. In other words, take the RAM out of the machine (where the code ran fine) and put in the machine where the code slowed to a crawl. Likewise, put the RAM (taken out of the machine where the code slowed to a crawl) and put that in the machine where the code ran fine. Run the code on both machines again. If the problem "follows the RAM" it's probably safe to assume it's memory. If you still have the issue after the memory swap, I'd follow the advice alex offered.
If the problem does appear to be the memory, call some PC shops and ask if they charge to test RAM. If you don't find anyone who tests the RAM for free, I'd probably just buy a new "stick" since it's so cheap right now.
Although it's a possibility that this is memory related, I have a feeling it's software related.
Alex, what function does the .emf perform

----- Paul W Smith wrote: ----

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 o
RAM, while the machine with the problem apparently has 512Mb. On the olde
machine the code operates without a problem, so it looks like I have
problem with my RAM, so your advice was invaluable

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

Paul Smit


"Tony" wrote in messag
..
From a Tech Support standpoint when my users complain that any applicatio
is suddenly slowed down, the first thing I do is delete all of the tmp file
on the machine. When it comes to Excel, their complaint is generally tha
EVERY Excel file opens and responds slowly. Even though you indicated tha
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 brough
Excel to a crawl and once I deleted those, the problem cleared u
immediately
Assumimg it's not related to your code, it could also be memory related i
the sense that after the routine runs for so long, it begins accessing a
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 tryin
unless you're convinced that it's somewhere in your code
Ton

This is just a guess of course. Would both machines be set to using the
same Printer Driver? On the "slower" machine, are you able to select a
different Printer? Is your Printer Driver current?
Just something to try...Good luck.

--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Paul W Smith" wrote in message
...
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,
..emf files are small graphic files which depict controls and other graphic
contents which exist on worksheets (I am not sure abour userforms) . If you
have an excel file open which has buttons, etc, goto the WINDOWS/TEMP
directory, select an .emf file, and look at the depiction in your explorer
window.

Apparently, there is only a limited amount of memory that excel can use to
hold these (~1 Meg?), and if there are a lot of these already in the TEMP
directory when you open excel, the memory available to access the valid
..emfs for your app is restricted. Excel then has to use disc access to get
these little graphic files, and that slows down excel critically. (.emf
files are not cleaned out of the TEMP directory whenever there is an excel
crash).

From personal experience, I had an application in the situation described
which took multiplle hours to run, when it could normally have run in a
minute or so.

By the way, adding RAM won't help (if .emfs in the TEMP directory are the
issue). The source of the problem is excel's limitation.

Another reference is www.decisionmodels.com . Look under the topic
"Bottlenecks/Size"

Alex J


"Tony" wrote in message
...
Paul,

I would not take the PC in for repair at this point as the problem is not
yet isolated. It still could be the memory, but I'm more inclined to go with
the explanation offered by Alex and would exhaust his suggestions first.
One thing you can do is swap the memory (assuming it's the same type of RAM
in both machines that you ran the code on) in the machines. In other words,
take the RAM out of the machine (where the code ran fine) and put in the
machine where the code slowed to a crawl. Likewise, put the RAM (taken out
of the machine where the code slowed to a crawl) and put that in the machine
where the code ran fine. Run the code on both machines again. If the
problem "follows the RAM" it's probably safe to assume it's memory. If you
still have the issue after the memory swap, I'd follow the advice alex
offered.
If the problem does appear to be the memory, call some PC shops and ask if
they charge to test RAM. If you don't find anyone who tests the RAM for
free, I'd probably just buy a new "stick" since it's so cheap right now.
Although it's a possibility that this is memory related, I have a feeling
it's software related.
Alex, what function does the .emf perform?

----- Paul W Smith wrote: -----

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