Automatically hide and unhide rows on a worksheet

monkeyspank

Board Regular
Joined
Feb 12, 2014
Messages
72
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?
VBA Code:
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?
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Row 6 or row 7?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,448
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,542
Messages
5,548,634
Members
410,861
Latest member
Victor96
Top