Screen Flickers and slows down worksheet functions

[TG]

Hello,

I have a code:

Private Sub Worksheet_Calculate()
Const MyCells As String = "c8,b55,b56,b57" 'adjust the range to suit
On Error GoTo stoppit
Application.EnableEvents = False
For Each cell In Me.Range(MyCells)
With cell
If .Value = "" Then
.EntireRow.Hidden = True
Else
.EntireRow.Hidden = False
End If
End With
Next
stoppit:
Application.EnableEvents = True
End Sub

That luckily I was able to find in this Forum. The problem is that this code
makes my worksheets run really slow. Every time the macro updates the whole
workbook slows down, I cannot type of make any changes till the macro is done
updating. This sometimes takes up to 5 seconds and makes the screen flickers.
Is there any way to stop the flickering and speed up the process without
compromisisng the code?

Thank you very much,
TG

 

[Gary''s Student]

Private Sub Worksheet_Calculate()
Const MyCells As String = "c8,b55,b56,b57" 'adjust the range to suit
On Error GoTo stoppit
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each cell In Me.Range(MyCells)
With cell
If .Value = "" Then
.EntireRow.Hidden = True
Else
.EntireRow.Hidden = False
End If
End With
Next
stoppit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[TG]

Hey Gary, thanks for yourhelp but it still seems slow (about the same).
any other suggestions?

 

[Gary''s Student]

Just be sure you are not using the =SUBTOTAL() function anywhere in your
worksheet. You can get into a REALLY bad loop if the macro hides/unhides
rows and this causes SUBTOTAL to re-calculate and this causes the macro to
re-enter, etc.....

 

[Gord Dibben]

The little bit of work the code does is not the cause of the slowdown.

You're only hiding/unhiding 4 rows.

What else do you have in your workbook?

The entire workbook is calculating so you could have calculations on other
sheets that take the time.

Or other calculations on the sheet with the code.

Have you tried the code in a workbook with just one sheet?

It is possible to disable calculation on other sheets when you activate the
sheet in question. But you have re-enable, you can't force a re-calc of
those other sheets.

If "c8,b55,b56,b57" are referencing other sheets' cells then disabling
calculation would be a non-starter.


Gord Dibben MS Excel MVP

 

[TG]

I have made sure that am not using the=subtotal(). I have noticed that once I
close and reopen the spreadsheet it starts to run much faster, but as I
continue to use it it starts to slow down quite a bit. What can i do to clear
the "buffer" of memory in my code, so everytime it runs the macro it sees it
as a fresh run. The code utilize is on the original message.
Thanks.
TG