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