monkeyspank
Board Regular
- Joined
- Feb 12, 2014
- Messages
- 73
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?
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?
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?