Hiding rows - Excel 2000, 2002 vs 2003

[Guest]

Dear All,

Could anyone please tell me why the following code is running instantly in
Excel 2000 and 2002 but takes minutes in Excel 2003. The named range
Countries is only a little bit more than 40 rows.

Sub Mask_Countries()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
ActiveSheet.DisplayPageBreaks = False

For Each c In [Countries].Cells
If (c.Value) = Empty Then
c.EntireRow.Hidden = True
End If
Next c

ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

If I turn the automatic calculation off in Excel 2003 it runs a lot faster
but not instantly as in Excel 2000 and 2003.

 

[Tom Ogilvy]

In xl2003, hiding rows can cause a recalculation and in earlier versions, it
didn't. This is because the subtotal formula was modified to work with
hidden rows rather than just rows hidden by a filter. I suspect this is
the major cause.

You could do

Sub Mask_Countries()
Dim c as Range
Application.ScreenUpdating = False
ActiveSheet.Unprotect
ActiveSheet.DisplayPageBreaks = False
On Error Resume Next
set c = Range("Countries").SpecialCells(xlBlanks)
On Error goto 0
if not c is nothing then _
c.EntireRow.Hidden = True
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

Also
Using [Countries] is slower than using Range("Countries") - although I
wouldn't expect the difference to be noticeable, maybe it is.

 

[Guest]

Thank you very much Tom - that's very helpful.
--
Regards,

Martin

In xl2003, hiding rows can cause a recalculation and in earlier versions, it
didn't. This is because the subtotal formula was modified to work with
hidden rows rather than just rows hidden by a filter. I suspect this is
the major cause.

You could do

Sub Mask_Countries()
Dim c as Range
Application.ScreenUpdating = False
ActiveSheet.Unprotect
ActiveSheet.DisplayPageBreaks = False
On Error Resume Next
set c = Range("Countries").SpecialCells(xlBlanks)
On Error goto 0
if not c is nothing then _
c.EntireRow.Hidden = True
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

Also
Using [Countries] is slower than using Range("Countries") - although I
wouldn't expect the difference to be noticeable, maybe it is.

--
Regards,
Tom Ogilvy

Dear All,

Could anyone please tell me why the following code is running instantly in
Excel 2000 and 2002 but takes minutes in Excel 2003. The named range
Countries is only a little bit more than 40 rows.

Sub Mask_Countries()
Application.ScreenUpdating = False
ActiveSheet.Unprotect
ActiveSheet.DisplayPageBreaks = False

For Each c In [Countries].Cells
If (c.Value) = Empty Then
c.EntireRow.Hidden = True
End If
Next c

ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

If I turn the automatic calculation off in Excel 2003 it runs a lot faster
but not instantly as in Excel 2000 and 2003.