Inexplicable difference in row hiding speed - identical code, identical machines!

[Matt Larkin]

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

 

[Frank Kabel]

Hi
try disabling screenupdating at the beginning and set the calculation
mode to manual

 

[Jim Cone]

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

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 -

 

[Tom Ogilvy]

Make sure pagebreaks aren't visible. If they are, this can have a
deleterious effect.

 

[Niek Otten]

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]

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