Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inexplicable difference in row hiding speed - identical code, identical machines!
I have some code which loops through a variety of cells determining
whether or not to hide them. I don't think that the code is particularly inefficient (though by no means perfect), but what is annoying me is that two machines which are physically (i.e. processor, HDD, RAM) identical, running the same SP of Excel 2000 on the same OS run at markedly different speeds. Both machines are Compaq P4 machines, physically identical inside and out! (XL 2000, ver 9.0.6926 SP3, Win2K) The whole process on my "old" PC took about 20 seconds to run. The whole process on my "new" PC takes closer to 5 minutes. Is there anything "key" in the Tools, Options (or similar) setups that might create this difference? The code is below. I've commented out the references to screenupdating and calculation modes as changing either / both of these makes no discernable difference to the speed differential. Just as a plain old user rightclicking and choosing to hide the offending rows also exhibits the same turgid speed. Is there any "dump" of Excel's settings that I can extract to see if there are any differences? Cheers! Matt Sub PrintAllDMDReports_pdf() Dim CurCell As Range Dim DMDCell As Range Dim pdfLocation As String Dim WeekNo As Integer pdfLocation = Sheets("PDF Printing").Range("A11").Value WeekNo = InputBox("Enter the current week no (e.g. 3)", "Week Number") 'Loops through each DMD in the structure and prints 'Application.ScreenUpdating = False For Each CurCell In Range(DMDRange) SelectedDMD = CurCell.Value Sheets("DMD Summary").Activate Range("C2") = SelectedDMD Range("D2").Activate 'Calculate 'Loops through to find #N/A's or other errors and hides them 'Has to be done this way to avoid problems for Excel 97 users as well For Each DMDCell In Range("B6:B13,B22:B37") N_A_DMD = DMDCell.Value If IsError(N_A_DMD) Then 'ML Added With Application '.Calculation = xlManual End With If N_A_DMD = CVErr(xlErrNA) Then DMDCell.Rows.Hidden = True End If 'ML Added With Application '.Calculation = xlAutomatic End With End If Next DMDCell 'Prints them to the pdf printer and renames to create unique files Sheets("DMD Summary").PrintOut Copies:=1, Collate:=True 'dir (c:\documents and settings\ Name pdfLocation & "Week " & WeekNo & ".pdf" As "h:\DMDs\" & SelectedDMD & ".pdf" Range("B6:B13,B22:B37").Rows.Hidden = False Next 'Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inexplicable difference in row hiding speed - identical code, identical machines!
Hi
try disabling screenupdating at the beginning and set the calculation mode to manual -- Regards Frank Kabel Frankfurt, Germany "Matt Larkin" schrieb im Newsbeitrag m... I have some code which loops through a variety of cells determining whether or not to hide them. I don't think that the code is particularly inefficient (though by no means perfect), but what is annoying me is that two machines which are physically (i.e. processor, HDD, RAM) identical, running the same SP of Excel 2000 on the same OS run at markedly different speeds. Both machines are Compaq P4 machines, physically identical inside and out! (XL 2000, ver 9.0.6926 SP3, Win2K) The whole process on my "old" PC took about 20 seconds to run. The whole process on my "new" PC takes closer to 5 minutes. Is there anything "key" in the Tools, Options (or similar) setups that might create this difference? The code is below. I've commented out the references to screenupdating and calculation modes as changing either / both of these makes no discernable difference to the speed differential. Just as a plain old user rightclicking and choosing to hide the offending rows also exhibits the same turgid speed. Is there any "dump" of Excel's settings that I can extract to see if there are any differences? Cheers! Matt Sub PrintAllDMDReports_pdf() Dim CurCell As Range Dim DMDCell As Range Dim pdfLocation As String Dim WeekNo As Integer pdfLocation = Sheets("PDF Printing").Range("A11").Value WeekNo = InputBox("Enter the current week no (e.g. 3)", "Week Number") 'Loops through each DMD in the structure and prints 'Application.ScreenUpdating = False For Each CurCell In Range(DMDRange) SelectedDMD = CurCell.Value Sheets("DMD Summary").Activate Range("C2") = SelectedDMD Range("D2").Activate 'Calculate 'Loops through to find #N/A's or other errors and hides them 'Has to be done this way to avoid problems for Excel 97 users as well For Each DMDCell In Range("B6:B13,B22:B37") N_A_DMD = DMDCell.Value If IsError(N_A_DMD) Then 'ML Added With Application '.Calculation = xlManual End With If N_A_DMD = CVErr(xlErrNA) Then DMDCell.Rows.Hidden = True End If 'ML Added With Application '.Calculation = xlAutomatic End With End If Next DMDCell 'Prints them to the pdf printer and renames to create unique files Sheets("DMD Summary").PrintOut Copies:=1, Collate:=True 'dir (c:\documents and settings\ Name pdfLocation & "Week " & WeekNo & ".pdf" As "h:\DMDs\" & SelectedDMD & ".pdf" Range("B6:B13,B22:B37").Rows.Hidden = False Next 'Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inexplicable difference in row hiding speed - identical code, identical machines!
Matt,
It is good practice to use "Option Explicit" at the top of each module. What follows is my slightly modifed version of your code. I have assumed that "DMDrange" is a named range. I can't test the code so please let us know how it works for you. '----------------------------------------- Sub PrintAllDMDReports_pdf() Dim CurCell As Range Dim DMDCell As Range Dim pdfLocation As String Dim WeekNo As Integer Dim SelectedDMD As Variant'* Dim N_A_DMD As Variant'* pdfLocation = Sheets("PDF Printing").Range("A11").Value WeekNo = _ InputBox("Enter the current week no (e.g. 3)", "WeekNumber ") 'Loops through each DMD in the structure and prints Application.ScreenUpdating = False Application.Calculation = xlManual For Each CurCell In Range(DMDrange) SelectedDMD = CurCell.Value Sheets("DMD Summary").Activate Range("C2").Value = SelectedDMD Range("D2").Activate ' Loops to find #N/A's or other errors and hides them ' Done this way to avoid problems for Excel 97 users as well For Each DMDCell In Range("B6:B13,B22:B37") N_A_DMD = DMDCell.Value If IsError(N_A_DMD) Then If N_A_DMD = CVErr(xlErrNA) Then ActiveSheet.DisplayPageBreaks = False'* DMDCell.Rows.Hidden = True End If End If Next DMDCell ' Prints to the pdf printer & renames to create unique files Sheets("DMD Summary").PrintOut Copies:=1, Collate:=True 'dir (c:\documents and settings\Name pdfLocation & _ '"Week " & WeekNo & ".pdf" As _ '"h:\DMDs\" & SelectedDMD & ".pdf" ActiveSheet.DisplayPageBreaks = False'* Range("B6:B13,B22:B37").Rows.Hidden = False Next Application.Calculation = xlAutomatic Application.ScreenUpdating = True End Sub '----------------------------------------- Regards, Jim Cone San Francisco, CA "Matt Larkin" wrote in message m... I have some code which loops through a variety of cells determining whether or not to hide them. I don't think that the code is particularly inefficient (though by no means perfect), but what is annoying me is that two machines which are physically (i.e. processor, HDD, RAM) identical, running the same SP of Excel 2000 on the same OS run at markedly different speeds. Both machines are Compaq P4 machines, physically identical inside and out! (XL 2000, ver 9.0.6926 SP3, Win2K) The whole process on my "old" PC took about 20 seconds to run. The whole process on my "new" PC takes closer to 5 minutes. Is there anything "key" in the Tools, Options (or similar) setups that might create this difference? The code is below. I've commented out the references to screenupdating and calculation modes as changing either / both of these makes no discernable difference to the speed differential. Just as a plain old user rightclicking and choosing to hide the offending rows also exhibits the same turgid speed. Is there any "dump" of Excel's settings that I can extract to see if there are any differences? Cheers! Matt -snip - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inexplicable difference in row hiding speed - identical code, identical machines!
Make sure pagebreaks aren't visible. If they are, this can have a
deleterious effect. -- Regards, Tom Ogilvy "Matt Larkin" wrote in message m... I have some code which loops through a variety of cells determining whether or not to hide them. I don't think that the code is particularly inefficient (though by no means perfect), but what is annoying me is that two machines which are physically (i.e. processor, HDD, RAM) identical, running the same SP of Excel 2000 on the same OS run at markedly different speeds. Both machines are Compaq P4 machines, physically identical inside and out! (XL 2000, ver 9.0.6926 SP3, Win2K) The whole process on my "old" PC took about 20 seconds to run. The whole process on my "new" PC takes closer to 5 minutes. Is there anything "key" in the Tools, Options (or similar) setups that might create this difference? The code is below. I've commented out the references to screenupdating and calculation modes as changing either / both of these makes no discernable difference to the speed differential. Just as a plain old user rightclicking and choosing to hide the offending rows also exhibits the same turgid speed. Is there any "dump" of Excel's settings that I can extract to see if there are any differences? Cheers! Matt Sub PrintAllDMDReports_pdf() Dim CurCell As Range Dim DMDCell As Range Dim pdfLocation As String Dim WeekNo As Integer pdfLocation = Sheets("PDF Printing").Range("A11").Value WeekNo = InputBox("Enter the current week no (e.g. 3)", "Week Number") 'Loops through each DMD in the structure and prints 'Application.ScreenUpdating = False For Each CurCell In Range(DMDRange) SelectedDMD = CurCell.Value Sheets("DMD Summary").Activate Range("C2") = SelectedDMD Range("D2").Activate 'Calculate 'Loops through to find #N/A's or other errors and hides them 'Has to be done this way to avoid problems for Excel 97 users as well For Each DMDCell In Range("B6:B13,B22:B37") N_A_DMD = DMDCell.Value If IsError(N_A_DMD) Then 'ML Added With Application '.Calculation = xlManual End With If N_A_DMD = CVErr(xlErrNA) Then DMDCell.Rows.Hidden = True End If 'ML Added With Application '.Calculation = xlAutomatic End With End If Next DMDCell 'Prints them to the pdf printer and renames to create unique files Sheets("DMD Summary").PrintOut Copies:=1, Collate:=True 'dir (c:\documents and settings\ Name pdfLocation & "Week " & WeekNo & ".pdf" As "h:\DMDs\" & SelectedDMD & ".pdf" Range("B6:B13,B22:B37").Rows.Hidden = False Next 'Application.ScreenUpdating = True End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inexplicable difference in row hiding speed - identical code, identical machines!
Hi Matt,
Not sure it has anything to do with your problem, but why not share it. I once had a table with formulas, about 200 columns, almost 500 rows (months, 40 years). I was interested in one cell in the last row (life insurance, you guessed right). So I split the screen to show top and bottom row. It seems that Excel now thinks it has to format all rows for screen presentation (first to last) and it took minutes to calculate the table instead of seconds (allright, this was Pentium III 50Mhz times) Maybe something similar happens in your case. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Matt Larkin" wrote in message m... I have some code which loops through a variety of cells determining whether or not to hide them. I don't think that the code is particularly inefficient (though by no means perfect), but what is annoying me is that two machines which are physically (i.e. processor, HDD, RAM) identical, running the same SP of Excel 2000 on the same OS run at markedly different speeds. Both machines are Compaq P4 machines, physically identical inside and out! (XL 2000, ver 9.0.6926 SP3, Win2K) The whole process on my "old" PC took about 20 seconds to run. The whole process on my "new" PC takes closer to 5 minutes. Is there anything "key" in the Tools, Options (or similar) setups that might create this difference? The code is below. I've commented out the references to screenupdating and calculation modes as changing either / both of these makes no discernable difference to the speed differential. Just as a plain old user rightclicking and choosing to hide the offending rows also exhibits the same turgid speed. Is there any "dump" of Excel's settings that I can extract to see if there are any differences? Cheers! Matt Sub PrintAllDMDReports_pdf() Dim CurCell As Range Dim DMDCell As Range Dim pdfLocation As String Dim WeekNo As Integer pdfLocation = Sheets("PDF Printing").Range("A11").Value WeekNo = InputBox("Enter the current week no (e.g. 3)", "Week Number") 'Loops through each DMD in the structure and prints 'Application.ScreenUpdating = False For Each CurCell In Range(DMDRange) SelectedDMD = CurCell.Value Sheets("DMD Summary").Activate Range("C2") = SelectedDMD Range("D2").Activate 'Calculate 'Loops through to find #N/A's or other errors and hides them 'Has to be done this way to avoid problems for Excel 97 users as well For Each DMDCell In Range("B6:B13,B22:B37") N_A_DMD = DMDCell.Value If IsError(N_A_DMD) Then 'ML Added With Application '.Calculation = xlManual End With If N_A_DMD = CVErr(xlErrNA) Then DMDCell.Rows.Hidden = True End If 'ML Added With Application '.Calculation = xlAutomatic End With End If Next DMDCell 'Prints them to the pdf printer and renames to create unique files Sheets("DMD Summary").PrintOut Copies:=1, Collate:=True 'dir (c:\documents and settings\ Name pdfLocation & "Week " & WeekNo & ".pdf" As "h:\DMDs\" & SelectedDMD & ".pdf" Range("B6:B13,B22:B37").Rows.Hidden = False Next 'Application.ScreenUpdating = True End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Inexplicable difference in row hiding speed - identical code, identical machines!
"Tom Ogilvy" wrote in message ...
Make sure pagebreaks aren't visible. If they are, this can have a deleterious effect. Tom / Jim As usual, spot on - although this option is turned off in the workbook, and by default in the application, it appears that it turns itself on when I changed printer to the Acrobat Distiller printer that we use to create pdfs. Turning this off has the desired effect of returning both PCs to operating at the same speed for this workbook. (For reference, the item was in tools / options / view - under Window Options, unticked page breaks) Obviously I shall add this to the code (as per Jim's note). Thanks for the option explicit tip too - something I do usually do, but this was a bit of a quick fix, so naming conventions and variable declaration got a bit sidelined!! Many thanks! Matt |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting Identical Entries | Excel Worksheet Functions | |||
Looking Up Datas when Key Values are Identical | Excel Worksheet Functions | |||
Why do I get different sums on identical columns. | Excel Discussion (Misc queries) | |||
Linking two identical charts | Charts and Charting in Excel | |||
two identical scripts, one with problem, the other with NO?! | Excel Programming |