Hide rows that equal 0

[meangreen]

In R25:R68 I have a formula that reluts in a "1" or a "0". The formula in R25 would be this: =if(A25<=$B$7,1,0). The formula in R26 would be the same but would reference A26 and so on. Basically, if any cell in A25:A68 is less than B7 then the corresponding cell in R25:R68 will be 1. If greater than it will equal 0.

Now I want to create a macro that will hide the rows from 25 thru 68 that have a 0 in coulmn R. They would also need to unhide as B7 will be changing all of the time. Any ideas?

 

[Von Pookie]

Untested, but how about:

Dim lRow As Long

'loop through rows 25-68
For lRow = 25 To 68
    'if col A of row <= B7
    If Cells(lRow, 1) <= Range("B7") Then
        'unhide row
        Rows(lRow).Hidden = False
    Else
        'hide row
        Rows(lRow).Hidden = True
    End If
Next lRow

 

[meangreen]

That almost works, but it's not hiding/unhiding when I change the value in B7. I have to manually run the macro to make it work.

 

[meangreen]

Is there a way to adjust the code above so that any time the value in B7 changes the appropriate rows will hide/unhide? Currently I would have to create a button or something for it to work, but I would really like it to be automatic.

I don't know if it matters but B7 is a drop down list.

 

[schielrn]

You could put the code in a worksheet_change event. And just check if the target cell is "$B$7"

Maybe try something like this, I didn't test it thoguh:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$B$7" Then
Dim lRow As Long
'loop through rows 25-68
For lRow = 25 To 68
    'if col A of row <= B7
    If Cells(lRow, 1) <= Range("B7") Then
        'unhide row
        Rows(lRow).Hidden = False
    Else
        'hide row
        Rows(lRow).Hidden = True
    End If
Next lRow
End If
Application.EnableEvents = True
End Sub

Hope that helps.

 

[meangreen]

Perfect. Thank you.