Unhide or hide rows based on cell values in each row

[jkehoe]

Hi all - my first post here.

I have a worksheet with a series of sequential numbers in Column F, typically running from about 25 or 30 in Row 6, and increasing by 1 in each row below that, to a maximum number in Row 85. I'd like to hide the rows in which the number in Column F is greater than 90. The procedure needs to run whenever the sheet is calculated. The values in the column can change based on either of 2 inputs on another sheet, so I also need the procedure to unhide rows in which the number is 90 or less. The code I have so far is:

Private Sub Worksheet_Calculate()
    Dim Cl As Range
    For Each Cl In Range("F6:F85")
        Cl.EntireRow.Hidden = False
        Cl.EntireRow.Hidden = Cl.Value > 90
    Next Cl
End Sub

... But it's not working. I get an "application-defined or object-defined" error. Can anyone see what I'm missing here?

BTW - I've also tried calling this procedure from Sheet1 (using the Worksheet_Change event), but I can't get it to run the code on Sheet2. To be clear - there are 2 inputs on Sheet1, and I want Sheet2's row-unhiding/hiding procedure to run if either input changes.

 

[Fluff]

There's nothing wrong with the code, but you don't need the first the first line in the loop, you can just use

Private Sub Worksheet_Calculate()
    Dim Cl As Range
    For Each Cl In Range("F6:F85")
        Cl.EntireRow.Hidden = Cl.Value > 90
    Next Cl
End Sub

which line is highlighted when you get the error?

 

[jkehoe]

It's the line you've identified which has been highlighted before, but actually right now when I ran it to check on this, it didn't highlight and I can no longer select any cells anywhere.

Without the first line in the loop, the rows only hide, and if the values subsequently change to =<90, the rows won't "unhide", will they?

 

[Fluff]

if the values subsequently change to =<90, the rows won't "unhide", will they?

Yes they will

 

[jkehoe]

OK I removed the line but now when I try to run it or even save the workbook I get the following error: "Run-time error '1004': Unable to set the Hidden property of the Range class".

Thanks, BTW, for helping with this!

 

[jkehoe]

It's highlighting the "Cl.EntireRow.Hidden = Cl.Value > 90" line.

 

[Fluff]

Is your sheet protected & do you have any merged cells?

 

[jkehoe]

Not protected, and no merged cells on Sheet2. When I mouse over the line in question, there's a callout which says "Unable to get the hidden property of the range class".

 

[Fluff]

How about

Private Sub Worksheet_Calculate()
    Dim Cl As Range
    Application.EnableEvents = False
    For Each Cl In Range("F6:F85")
        Cl.EntireRow.Hidden = Cl.Value > 90
    Next Cl
    Application.EnableEvents = True
End Sub

 

[jkehoe]

OK that did it. Thanks so much! Do you know what the Application.EnableEvents switch would have stopped from causing the error?