Simple Button Causing Problems

[tkdpale]

I am creating a button in excel to hide any rows that are missing data in the first column. I've got the code worked out I believe, but I can't make the button work. The code is as followed:

Sub Button74_Click()
For Each r In Range("A10:A164")
If r.Value = "True" Then
r.EntireRow.Hidden = True
Else
r.EntireRow.Hidden = False
End If
End Sub

I keep getting the error message "Compile Error: For without Next". I'm not sure what this means or how to fix it. Can somebody help?

 

[MickG]

Try:-

End If
Next r
End Sub

Mick

 

[tkdpale]

Awesome, now I'm not getting an error, but the button doesn't hide anything. I have a logic test for each row to tell me whether or not it need hidden or not and it looks like:

=AND(ISBLANK(C10),ISBLANK(K10),ISBLANK(E10))

Example for row 10. This formula outputs either True or False. Hence why I was using the logic test in the button:

If r.Value = "True" Then

I'm not sure why it isn't hiding the cells that output True.

 

[sous2817]

I think you can simplify your code a bit by doing something like:

Dim r As Range

Application.ScreenUpdating = False


For Each r In Range("A10:A164")
r.EntireRow.Hidden = r.Value
Next r
Application.ScreenUpdating = True

Actually the simplist way would be to a filter on column A....

Nonetheless, you may want to qualify your range reference (if the button is on a different sheet than the rows you're hidding, it won't work as you expect).

 

[VoG]

Try

Sub Button74_Click()
For Each r In Range("A10:A164")
    r.EntireRow.Hidden = r.Value = True
Next r
End Sub

 

[tkdpale]

Got it working, thanks everyone. This site is pretty frickin awesome.