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't 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 cells that have formulas in them

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

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

Thanks for your time

[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.


--
Regards,
Tom Ogilvy


"mav93" <[email protected]> wrote in
message news:[email protected]...
>
> HI everyone,
>
> I have a bit of code that I have been trying to get to work but haven't
> 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 cells
> that have formulas in them
>
> when I run the above code any change that I make to the workbook not
> just the worksheet that the code is in weather or not it effects cells
> L9 - L208 causes lines to be hidden or unhidden and my screen to start
> blinking. I have to hit the escape key and end or debug to regain
> control of excel.
>
> Any ideas where I went wrong would be appreciated, I pretty new to VBA
> so I'm sorry that I don't know how to explain my self better.
>
> Thanks for your time
>
>
> --
> mav93
> ------------------------------------------------------------------------
> mav93's Profile:
http://www.excelforum.com/member.php...o&userid=31592
> View this thread: http://www.excelforum.com/showthread...hreadid=512860
>

[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

--
Regards,
Tom Ogilvy


"mav93" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> mav93
> ------------------------------------------------------------------------
> mav93's Profile:
http://www.excelforum.com/member.php...o&userid=31592
> View this thread: http://www.excelforum.com/showthread...hreadid=512860
>