Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows - Excel 2000, 2002 vs 2003
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. -- Regards, Martin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows - Excel 2000, 2002 vs 2003
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 "Martin" wrote in message ... 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. -- Regards, Martin |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding rows - Excel 2000, 2002 vs 2003
Thank you very much Tom - that's very helpful.
-- Regards, Martin "Tom Ogilvy" wrote: 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 "Martin" wrote in message ... 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. -- Regards, Martin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding even (or odd) rows in excel 2003 | Excel Discussion (Misc queries) | |||
Recalculation on hiding rows in Excel 2003 | Excel Discussion (Misc queries) | |||
Simple Macro, works in Excel 2002, 2003 but won't work in 2000 | Excel Programming | |||
Using OWC VBA programs in excel 2000/2002/2003 | Excel Programming | |||
Can you use excel 2003 version with excel 2002 and 2000 on a netw. | Excel Discussion (Misc queries) |