Automatically hide and unhide rows on a worksheet

[monkeyspank]

Morning Everyone,

I have created the below code for hiding and unhiding rows but it taking a long time for it to run. Is there anyway to write the below code shorter?

Private Sub Worksheet_Calculate()

If Sheets("Space Sensor Verification").Range("$Q$6") = "No" Then

Rows("7:7").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$6") = "Yes" Then


Rows("7:7").EntireRow.Hidden = False

End If

If Sheets("Space Sensor Verification").Range("$Q$7") = "No" Then

Rows("8:8").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$7") = "Yes" Then


Rows("8:8").EntireRow.Hidden = False

End If

If Sheets("Space Sensor Verification").Range("$Q$8") = "No" Then

Rows("9:9").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$8") = "Yes" Then


Rows("9:9").EntireRow.Hidden = False

End If

If Sheets("Space Sensor Verification").Range("$Q$9") = "No" Then

Rows("9:9").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$9") = "Yes" Then


Rows("9:9").EntireRow.Hidden = False

End If

If Sheets("Space Sensor Verification").Range("$Q$10") = "No" Then

Rows("10:10").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$10") = "Yes" Then


Rows("10:10").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$11") = "No" Then

Rows("11:11").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$11") = "Yes" Then


Rows("11:11").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$12") = "No" Then

Rows("12:12").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$12") = "Yes" Then


Rows("12:12").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$13") = "No" Then

Rows("13:13").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$13") = "Yes" Then


Rows("13:13").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$14") = "No" Then

Rows("14:14").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$14") = "Yes" Then


Rows("14:14").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$15") = "No" Then

Rows("15:15").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$15") = "Yes" Then


Rows("15:15").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$16") = "No" Then

Rows("16:16").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$16") = "Yes" Then


Rows("16:16").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$17") = "No" Then

Rows("17:17").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$17") = "Yes" Then


Rows("17:17").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$18") = "No" Then

Rows("18:18").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$18") = "Yes" Then


Rows("18:18").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$19") = "No" Then

Rows("19:19").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$19") = "Yes" Then


Rows("19:19").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$20") = "No" Then

Rows("20:20").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$20") = "Yes" Then


Rows("20:20").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$21") = "No" Then

Rows("21:21").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$21") = "Yes" Then


Rows("21:21").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$22") = "No" Then

Rows("22:22").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$22") = "Yes" Then


Rows("22:22").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$23") = "No" Then

Rows("23:23").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$23") = "Yes" Then


Rows("23:23").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$24") = "No" Then

Rows("24:24").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$24") = "Yes" Then


Rows("24:24").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$25") = "No" Then

Rows("25:25").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$25") = "Yes" Then


Rows("25:25").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$26") = "No" Then

Rows("26:26").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$26") = "Yes" Then


Rows("26:26").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$27") = "No" Then

Rows("27:27").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$27") = "Yes" Then


Rows("27:27").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$28") = "No" Then

Rows("28:28").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$28") = "Yes" Then


Rows("28:28").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$29") = "No" Then

Rows("29:29").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$29") = "Yes" Then


Rows("29:29").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$30") = "No" Then

Rows("30:30").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$30") = "Yes" Then


Rows("30:30").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$31") = "No" Then

Rows("31:31").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$31") = "Yes" Then


Rows("31:31").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$32") = "No" Then

Rows("32:32").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$32") = "Yes" Then


Rows("32:32").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$33") = "No" Then

Rows("33:33").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$33") = "Yes" Then


Rows("33:33").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$34") = "No" Then

Rows("34:34").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$34") = "Yes" Then


Rows("34:34").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$35") = "No" Then

Rows("35:35").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$35") = "Yes" Then


Rows("35:35").EntireRow.Hidden = False

End If
If Sheets("Space Sensor Verification").Range("$Q$36") = "No" Then

Rows("36:36").EntireRow.Hidden = True

ElseIf Sheets("Space Sensor Verification").Range("$Q$36") = "Yes" Then


Rows("36:36").EntireRow.Hidden = False

End If
End Sub

What I am attempting to achieve is hide and unhide based on a Yes/No in Column Q. Column Q has a formula in it, =IF('RTU Information '!A3>0,"Yes","No"), starting at Q7 and going down to Q36). I am only wanting to hide and unhide rows 7:36 based on if a value is entered on a different tab. I really hope this makes sense? Like I said, the code above works but it runs very very slow.

Any ideas?

 

[Fluff]

Does that make sense?

No, because you have still not answered my question.
If Q6 has "No" which row should be hidden, is it row 6, or row 7?

 

[monkeyspank]

Sorry.

If Q6 is "No" then it should be hidden

 

[Fluff]

Row 6 or row 7?

 

[monkeyspank]

Row 6 or row 7?

If Q6 is "No" then Row 6 should be hidden.

I got it working Thank You

 

[Fluff]

Glad you sorted it & thanks for the feedback.