Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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 -- rpw |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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 "rpw" wrote: 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 -- rpw |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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. --- 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 "rpw" wrote in message ... 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 -- rpw |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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" wrote: 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 "rpw" wrote: 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 -- rpw |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
You're welcome.
I am glad it worked on the first trial, 'cause i typed the code directly here without testing first :-) Sebastien "rpw" wrote: 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" wrote: 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 "rpw" wrote: 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 -- rpw |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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). "David McRitchie" wrote: 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.... 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 -- rpw |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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 "sebastienm" wrote in message ... You're welcome. I am glad it worked on the first trial, 'cause i typed the code directly here without testing first :-) Sebastien "rpw" wrote: 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" wrote: 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 "rpw" wrote: 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 -- rpw |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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). --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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" wrote in message ... 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). "David McRitchie" wrote: 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.... 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 -- rpw |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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. "Dana DeLouis" wrote: 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 "sebastienm" wrote in message ... You're welcome. I am glad it worked on the first trial, 'cause i typed the code directly here without testing first :-) Sebastien "rpw" wrote: 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" wrote: 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 "rpw" wrote: 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 -- rpw |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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" wrote: 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" wrote in message ... 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). "David McRitchie" wrote: 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.... 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 -- rpw |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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 "rpw" wrote in message ... 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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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. "David McRitchie" wrote: 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 "rpw" wrote in message ... 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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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 "rpw" wrote in message ... 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. "David McRitchie" wrote: 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 "rpw" wrote in message ... 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. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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.. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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. "David McRitchie" wrote: 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.. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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. "Dana DeLouis" wrote: 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 "rpw" wrote in message ... 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. "David McRitchie" wrote: 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 "rpw" wrote in message ... 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. |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Runs fast then slow
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: 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. "David McRitchie" wrote: 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.. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel runs slow | Excel Worksheet Functions | |||
Macros Running Sometimes Slow Sometimes Fast | Excel Discussion (Misc queries) | |||
Excel Runs Slow | Excel Discussion (Misc queries) | |||
Searches slow for some Fast for others? | Excel Worksheet Functions | |||
It was SLOW, now is FAST! | Excel Programming |