Runs fast then slow

[rpw]

Hi everyone,

I have this code that runs very fast the first time I open the workbook and
run it. All subsequent runs are about 1/2 to 1/3 the speed. I don't
understand why the slow down. Can someone explain it to me?

Would I be better off taking the hide/unhide rows portion of it and making
it a function and include it as part of the cell formula - would that slow
the workbook way down?

Thanks in advance for your responses. Here's the code:

Dim RowCounter As Integer
Dim BreakCounter As Integer
Dim CellValue As String

Worksheets("Summary Sheet").Activate
Worksheets("Summary Sheet").Range("A1").Select

For RowCounter = 1 To 976
CellValue = Selection
If CellValue <> "Skip" Then
Selection.EntireRow.Hidden = True
Selection.Offset(1, 0).Range("A1").Select
Else:
Selection.EntireRow.Hidden = False
Selection.Offset(1, 0).Range("A1").Select
End If
Next RowCounter

Worksheets("Summary Sheet").Range("p249").Select

For BreakCounter = 1 To 4
CellValue = Selection
If CellValue = "Break" Then
Selection.PageBreak = xlPageBreakManual
Else: Selection.PageBreak = xlPageBreakNone
End If
Selection.Offset(241, 0).Range("a1").Select
Next BreakCounter

ActiveSheet.PrintPreview

Worksheets("Summary Sheet").Range("a1:a1000").Select
Selection.EntireRow.Hidden = False
Worksheets("Basic Pricing").Activate
Worksheets("Basic Pricing").Range("d3").Select

 

[sebastienm]

Hi rpw,
1. You don't need to select a partyicular range to manipulate it:
Worksheets("Summary Sheet").Range("A1").EntireRow.Hidden=True
will hide the row even though Worksheet("Sheet1") is currently the active
one.
DOing some '.Select' greatly slows down a process.
2. Setting the Hidden property for each row individually slows things down
too. You could do it on the whole specific rows in one shot.
3. It is common thing to wrap your code between the 2 lines:
Application.ScreenUpdating=False
... code here
Application.ScreenUpdating=True
By doing so you 'freeze the excel window during , that is excel does
spend time refreshing the screen at each Hidden line.

So here some code for that. Try it in a separate Sub:
'---------------------------------------------
Dim RowCounter As Integer
Dim BreakCounter As Integer
Dim CellValue As String
Dim Cell as Range, RgToSearch as Range, RgResult as Range '<-------
Dim Wsh as Worksheet '<------

application.screeenUpdating=False '<-----

Set Wsh= Worksheets("Summary Sheet")
Set RgToSearch=Wsh.Range("A1:A976")

'Unhide all
RgToSearch.EntireRow.Hidden = False

'Search and Build range non-Skip
For Each Cell in rgToSearch.Cells
If Ucase(Cell.Text)<>"SKIP" Then
If RgResult Is Nothing then
Set RgResult=Cell
Else
Set RgResult= application.Union(RgResult, Cell)
End If
End If
Next Cell

'Hide Skips rows all at once
If Not RgResult Is Nothing then
RgResult.EntireRow.Hidden = True
End if

'page breaks
Set Cell = Wsh.Range("p249")
For BreakCounter = 1 To 4
Cell.PageBreak = iif(Cell.Text =
"Break",xlPageBreakManual,xlPageBreakNone)
Set Cell=Cell.Offset(241,0)
Next BreakCounter

Wsh.Activate 'Needs to be activate for PrintPreview i believe
Wsh.PrintPreview
Wsh.Range("a1:a1000").EntireRow.Hidden = False
Worksheets("Basic Pricing").Activate
Worksheets("Basic Pricing").Range("d3").Select

Application.ScreenUpdating=True 'not neccessary i believe
'----------------------------------------------------------------
I hope this helps,
Regards,
Sebastien

 

[David McRitchie]

You should avoid changing the selection within a macro
if you want to make bad coding run faster then turn off
screen updating and turn off calculation.
http://www.mvps.org/dmcritchie/excel/slowresp.htm
In fact you can sometimes beat out better that does not
turn off screen updating and turn off calculation, but that
doesn't really solve your problem.

 

[rpw]

Hi sebastienm,

Thanks for the quick response. It took me a while to 'read and understand'
the code (I'm not very good at this stuff obviously!). I used it and it runs
in about .75 seconds compared to the 'fast' version of mine which took maybe
2-3 seconds and the 'slow' version which took 8-10 seconds. Yours is so fast
that the screen update property doesn't need to be turned off.

Thank you for this!

 

[sebastienm]

You're welcome.
I am glad it worked on the first trial, 'cause i typed the code directly
here without testing first

Sebastien

 

[rpw]

Hi Mr. McRitchie,

Thank you for the quick response. I've asked more questions below. Please
keep in mind that I'm relatively new (and untrained) to coding (it took me a
couple of days to figure out the little bit that I posted earlier).

You should avoid changing the selection within a macro

Can you take the time to explain why?

if you want to make bad coding run faster then turn off
screen updating and turn off calculation.

I tried turning off screen updating and calculation and it seems to take as
long or maybe even longer. Can you explain "bad coding"?

but that doesn't really solve your problem.

I posted a question, not a problem, and my question has still not been
answered. Is there a reason why the code runs fast (2-3 seconds) the first
time it's run and slower (8-10 seconds) on subsequent runs? How/why does
'bad code' get worse when it's run a second or third time?

btw, I was happy with the speed as it was originally - I just didn't
understand why there were differences in running speed. The potential users
that I displayed my program to were astonished to see that excel could work
like that. It was rather fun to see their reactions to the screen update
(rows being hidden) as the program ran.

However, that being said, I am open to suggestions. e.g. another
respondent, sebastienm, provided an alternative to my original and I can see
that one of the main differences is that there is not any changing of the
selection within the macro (probably why it runs in the blink of an eye!). I
will study how that's accomplished and try to utilize that in the future.

thanks again,

rpw....

 

[Dana DeLouis]

Another variation to experiment with might be:

Dim r As Long
Const str As String = "Skip"

Application.ScreenUpdating = False
For r = 1 To 976
Rows(r).Hidden = StrComp(Cells(r, 1), str, vbTextCompare) <> 0
Next r

HTH
Dana DeLouis

 

[David McRitchie]

Looks like Sebastian gave you more efficient code and for the
some explanations look in the reference I gave you. By bad code
I meant changing the active cell and/or selection in macro is not
a good idea unless the purpose of the macro is to produce a
different selection. The reference was
http://www.mvps.org/dmcritchie/excel/slowresp.htm

And Dana just provide another solution which is going to be much
more efficient, but turning off calculation would still help (probably).

 

[Dana DeLouis]

This is just a guess of course on your timing differences.
When you work with "PageBreaks", I believe that Excel works some how with
your printer driver to arrive at some type of display. Some older printer
drivers do not work well...some are known to not release memory. As a
result, some programs slow down.
As a wild guess, you may want to check if there is a more recent printer
driver for your printer.
Good luck.

Dana DeLouis.

 

[rpw]

Hi Dana,

Thanks for the suggestion. Your code is shorter/cleaner/slicker than mine
but it still takes about the same amount of time to run. I'll see if I can't
incorporate some of your ideas with sebastienm's ideas.

 

[rpw]

Actually, I don't think the slow down is in that portion of the code. I
think it's in the changing of selection as Mr. McRitchie suggested.

 

[David McRitchie]

Neither Sebastian's code nor Dana's code is changing the selection.
But Dana's comment about page breaks might very well explain a lot
of things that you will also find in slowresp.htm on my site.

Turn off manually with Tools --> Options --> View --> (uncheck) Page Breaks,
or in VBA at the beginning of the most efficient code that works with
ActiveSheet.DisplayPageBreaks = False

 

[rpw]

Hi. Thanks for your continued input. I have looked at the links you
provided and there's quite a bit there to digest. Certainly, if the user's
machines are slower than mine, the lack of speed will be more pronounced so I
have to be wary of that.

Yes, I see that neither of their code is changing the selection - mine was.
When I ran Dana's code, it was as a separate sub and there was nothing about
page breaks included, yet it still took about the same perceived length of
time as my code. As I watch both mine and Dana's code run on the screen
update, the hiding of the rows is the slow part. When mine finishes hiding
rows, then the page break portion of the code is done in a fraction of a
second - I never 'see' it run, I only see the print preview with the breaks
in the proper locations.

Here's the way I understand the suggested code:

When Dana's code runs, it looks at a cell, sets the Hidden property to true
or false, then moves to the next cell to evaluate.

When sebastienm's code runs, if the cell doesn't have "Skip" as a value,
then the cell range is added to another range variable (RgResults). At the
end of the looping then the RgResults Hidden property is set to true and all
of those rows are hidden at once.

When I set my code to skip the Hidden instruction lines, it runs very fast
(still not as fast as sebastienm's, but almost).

My conclusion is that it's the hiding of the row on an individual basis that
is taking the most time.

I'm going to try to combine the simplicity of Dana's code with the
build-a-range-then-hide-it-all-at-once of sebastienm's code. I'll post it
here if I can get it to work well.

Neither Sebastian's code nor Dana's code is changing the selection.
But Dana's comment about page breaks might very well explain a lot
of things that you will also find in slowresp.htm on my site.

Turn off manually with Tools --> Options --> View --> (uncheck) Page Breaks,
or in VBA at the beginning of the most efficient code that works with
ActiveSheet.DisplayPageBreaks = False
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Actually, I don't think the slow down is in that portion of the code. I
think it's in the changing of selection as Mr. McRitchie suggested.

 

[Dana DeLouis]

Just an added idea. Both codes are setting the hidden property even if it
is not necessary. In this version, the setting of the hidden property is
only called if needed. The idea behind using Xor is that it returns True
if what you want is not what you have.

Sub Demo()
Dim r As Long 'Row
Dim TF As Boolean 'True / False
Const str As String = "Skip"

With Application
.ScreenUpdating = False
.Calculation = xlManual

For r = 1 To 976
TF = StrComp(Cells(r, 1), str, vbTextCompare) <> 0
If TF Xor Rows(r).Hidden Then Rows(r).Hidden = TF
Next r

.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

HTH
Dana DeLouis

Hi. Thanks for your continued input. I have looked at the links you
provided and there's quite a bit there to digest. Certainly, if the
user's
machines are slower than mine, the lack of speed will be more pronounced
so I
have to be wary of that.

Yes, I see that neither of their code is changing the selection - mine
was.
When I ran Dana's code, it was as a separate sub and there was nothing
about
page breaks included, yet it still took about the same perceived length of
time as my code. As I watch both mine and Dana's code run on the screen
update, the hiding of the rows is the slow part. When mine finishes
hiding
rows, then the page break portion of the code is done in a fraction of a
second - I never 'see' it run, I only see the print preview with the
breaks
in the proper locations.

Here's the way I understand the suggested code:

When Dana's code runs, it looks at a cell, sets the Hidden property to
true
or false, then moves to the next cell to evaluate.

When sebastienm's code runs, if the cell doesn't have "Skip" as a value,
then the cell range is added to another range variable (RgResults). At
the
end of the looping then the RgResults Hidden property is set to true and
all
of those rows are hidden at once.

When I set my code to skip the Hidden instruction lines, it runs very fast
(still not as fast as sebastienm's, but almost).

My conclusion is that it's the hiding of the row on an individual basis
that
is taking the most time.

I'm going to try to combine the simplicity of Dana's code with the
build-a-range-then-hide-it-all-at-once of sebastienm's code. I'll post it
here if I can get it to work well.

Neither Sebastian's code nor Dana's code is changing the selection.
But Dana's comment about page breaks might very well explain a lot
of things that you will also find in slowresp.htm on my site.

Turn off manually with Tools --> Options --> View --> (uncheck) Page
Breaks,
or in VBA at the beginning of the most efficient code that works
with
ActiveSheet.DisplayPageBreaks = False
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Actually, I don't think the slow down is in that portion of the code.
I
think it's in the changing of selection as Mr. McRitchie suggested.

 

[David McRitchie]

When you open your file Page Breaks will be off, when you print or
preview, or turn on page breaks they will be on and Excel will have
to spend more time figuring out what page something is on each time
you unhide a row.

If you want to actually compare times, there is code to check the time
difference between start and finish point that you include. You will find
the code near the end of the slowresp.htm page.

Whenever response questions are asked it is always a good idea to
include the version of Excel as well as the version of the operating
system. And as already pointed out the print drivers themselves could
be responsible for some of performance problems/solutions, but turning
of page breaks and screen updating should solve what you can see.
Used to be a lot of complaints about H-P printers, but I don't see such
complaints anymore, maybe that was mainly with Excel 95 and or
Windows 95 systems..

 

[rpw]

Thank you. While I don't have any perceptible speed problems with the page
break, it's possible that some of the users of my app will have older/slower
machines than I do and they might encounter some problems, so I'll go ahead
and take your suggestion on the turning on/off of page breaks.

I hadn't gotten down that far to see the timer code. I'll time things
later....

I'm running office 2003 on windows 2000 pro on a 1.8 ghz P4 with 512MB ram.

 

[rpw]

Hi Dana,

Thanks for this alternative. You've given me a lot of new (to me) ways of
doing things. Hopefully, I'll be able to put those ideas to good use before
I forget about them.

Thanks again for all of your input.

Just an added idea. Both codes are setting the hidden property even if it
is not necessary. In this version, the setting of the hidden property is
only called if needed. The idea behind using Xor is that it returns True
if what you want is not what you have.

Sub Demo()
Dim r As Long 'Row
Dim TF As Boolean 'True / False
Const str As String = "Skip"

With Application
.ScreenUpdating = False
.Calculation = xlManual

For r = 1 To 976
TF = StrComp(Cells(r, 1), str, vbTextCompare) <> 0
If TF Xor Rows(r).Hidden Then Rows(r).Hidden = TF
Next r

.Calculation = xlAutomatic
.ScreenUpdating = True
End With
End Sub

HTH
Dana DeLouis

Hi. Thanks for your continued input. I have looked at the links you
provided and there's quite a bit there to digest. Certainly, if the
user's
machines are slower than mine, the lack of speed will be more pronounced
so I
have to be wary of that.

Yes, I see that neither of their code is changing the selection - mine
was.
When I ran Dana's code, it was as a separate sub and there was nothing
about
page breaks included, yet it still took about the same perceived length of
time as my code. As I watch both mine and Dana's code run on the screen
update, the hiding of the rows is the slow part. When mine finishes
hiding
rows, then the page break portion of the code is done in a fraction of a
second - I never 'see' it run, I only see the print preview with the
breaks
in the proper locations.

Here's the way I understand the suggested code:

When Dana's code runs, it looks at a cell, sets the Hidden property to
true
or false, then moves to the next cell to evaluate.

When sebastienm's code runs, if the cell doesn't have "Skip" as a value,
then the cell range is added to another range variable (RgResults). At
the
end of the looping then the RgResults Hidden property is set to true and
all
of those rows are hidden at once.

When I set my code to skip the Hidden instruction lines, it runs very fast
(still not as fast as sebastienm's, but almost).

My conclusion is that it's the hiding of the row on an individual basis
that
is taking the most time.

I'm going to try to combine the simplicity of Dana's code with the
build-a-range-then-hide-it-all-at-once of sebastienm's code. I'll post it
here if I can get it to work well.

Neither Sebastian's code nor Dana's code is changing the selection.
But Dana's comment about page breaks might very well explain a lot
of things that you will also find in slowresp.htm on my site.

Turn off manually with Tools --> Options --> View --> (uncheck) Page
Breaks,
or in VBA at the beginning of the most efficient code that works
with
ActiveSheet.DisplayPageBreaks = False
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Actually, I don't think the slow down is in that portion of the code.
I
think it's in the changing of selection as Mr. McRitchie suggested.

 

[mglaguna]

Excuse me you all ... I took a read and I did not understand why one macros
runs very fast on a brand new file instead the same macro runs on older file
..... does not make any sense to me ... I did that few minutes ago; I have
this macro that imports one *.txt file, wich has about 20.000 rows into an
old file and it takes about 20 min to finish (!); I copied this code into
another flie (brand new one) and it tooks about 5 seconds (!!!!). Is thare
any explanation that would explain what is going on ? Best regards for you
all - Mauricio G. Laguna - S.P. - Brazil.

"rpw" escreveu: