Help with hiding rows by VBA

[mav93]

HI everyone,

I have a bit of code that I have been trying to get to work but haven'
had any luck.

Private Sub Worksheet_Calculate()
'Sheet module code, like: Sheet3!
Dim myRng As Range

Set myRng = Range("L9", Range("L208"))

Application.ScreenUpdating = False
For Each c In myRng
If c.Value = "" Then c.EntireRow.Hidden = True

If c.Value <> "" Then c.EntireRow.Hidden = False
Next c

Application.ScreenUpdating = True
End Sub


Cells L9 - L208 all have formula in them that are tied to other cell
that have formulas in them

when I run the above code any change that I make to the workbook no
just the worksheet that the code is in weather or not it effects cell
L9 - L208 causes lines to be hidden or unhidden and my screen to star
blinking. I have to hit the escape key and end or debug to regai
control of excel.

Any ideas where I went wrong would be appreciated, I pretty new to VB
so I'm sorry that I don't know how to explain my self better.

Thanks for your tim

 

[Tom Ogilvy]

Private Sub Worksheet_Calculate()
'Sheet module code, like: Sheet3!
Dim myRng As Range
On error goto ErrHandler
Application.EnableEvents = False
calc = Application.Calculation
Application.Calculation = xlManual
Set myRng = Range("L9", Range("L208"))

Application.ScreenUpdating = False
For Each c In myRng
If c.Value = "" Then c.EntireRow.Hidden = True

If c.Value <> "" Then c.EntireRow.Hidden = False
Next c

ErrHandler:
Application.Calculation = calc
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Might help.

 

[mav93]

thanks Tom the code worked great
I know that this maybe asking a lot but anytime that I enter a value
into any cell throughout my whole enitre workbook even ones not related
to the code and not in the same worksheet, my screen glitches and after
entering 250 rows of information the screen starts to look like a
strobe light.
Trust me I am extemely thankful for the fix you gave me I'm just
wondering if this glitch could be fixed as well.

Thanks for your time.

 

[Tom Ogilvy]

Think you just want to set calculation to Manual (in tools => options,
calculation tab) when you are entering data. Or use a macro to turn off
events.

Sub EventsOff()
Application.EnableEvents = False
End Sub

Sub EventsON()
Application.EnableEvents = True
End Sub