Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyResult As String
'first, unhide any rows that are currently hidden.
Rows("1:" & ActiveSheet.UsedRange.Rows.Count).EntireRow.Hidden = False
'Cells(91,1) mean the cell in the 91 st row and the 1 st column, ie A91
'MyResult is the variable that takes it value.
MyResult = Cells(91, 1).Value
'This is the spot where you'd add your other cases.
'Just keep adding your cases as in the examples. Be sure that all the cases are
'in between the Select and the End Select statements
Select Case MyResult
'Including the value 0 in case of a direct a = b type of thing; if you're confident that
'0 will never mean Empty String, remove it from the case
Case "", "None", "0"
Rows("92:110").EntireRow.Hidden = True
Case "2"
Rows("2:4").EntireRow.Hidden = True
Case "142"
Rows("22:45").EntireRow.Hidden = True
Case "xyz"
Rows("1:3").EntireRow.Hidden = True
'Case Else is a special case that happens if none of the above values are
'detected in cell A91. It is optional.
Case Else
Rows("91:91").EntireRow.Hidden = True
End Select
'If you have any rows that should be always hidden, you can
' "rehide" any rows that you want always hidden.
Rows("5:6").EntireRow.Hidden = True
End Sub
QUOTE]
Hi, I wonder if you could help. Sorry for hi-jacking your post but i've used this code edited to suit a worksheet that i'm making and it works great.
For example i've used the above to hide rows 20:24 based on a value in cell C7 (repeated for various cases)
Below this routine I'm pasting the same code and changing the variables this time to say something like: Hide row 36 based on the value in cell C6.
The problem is that it stops automatically working, it will work once when I close the VBA window but then if I change the value in C6 it no longer functions.
Can you please advise how I get it to automatically function? Hope i've explained this well enough!
This is what I have:
Private Sub Worksheet_Calculate()
Dim MyResult As String
Application.EnableEvents = False
'first, unhide any rows that are currently hidden.
'This first part will hide the rows associated with the panels depending on number of panels generated by the product code
''''''''''''''''''''''''''''''''''''''''''''''''''
'change the name from Sheet1 to your worksheets name.
''''''''''''''''''''''''''''''''''''''''''''''''''
Rows("1:" & Worksheets("Cutting Sheet for Std Systems").UsedRange.Rows.Count).EntireRow.Hidden = False
'Cells(91,1) mean the cell in the 91 st row and the 1 st column, ie A91
'MyResult is the variable that takes it value.
''''''''''''''''''''''''''''''''''''''''''''''''''
'change the name from Sheet1 to your worksheets name.
MyResult = Worksheets("Cutting Sheet for Std Systems").Cells(7, 3).Value
''''''''''''''''''''''''''''''''''''''''''''''''''
'This is the spot where you'd add your other cases.
'Just keep adding your cases as in the examples. Be sure that all the cases are
'in between the Select and the End Select statements
Select Case MyResult
'Including the value 0 in case of a direct a = b type of thing; if you're confident that
'0 will never mean Empty String, remove it from the case
Case "", "None", "0"
Rows("28:33").EntireRow.Hidden = True
Case "2"
Rows("28:33").EntireRow.Hidden = True
Case "3"
Rows("29:33").EntireRow.Hidden = True
Case "4"
Rows("30:33").EntireRow.Hidden = True
Case "6"
Rows("32:33").EntireRow.Hidden = True
Case "8"
Rows("33:34").EntireRow.Hidden = False
'Case Else is a special case that happens if none of the above values are
'detected in cell A91. It is optional.
'Case Else
'Rows("91:91").EntireRow.Hidden = True
End Select
'If you have any rows that should be always hidden, you can
' "rehide" any rows that you want always hidden.
'Rows("5:6").EntireRow.Hidden = True
Application.EnableEvents = True
End Sub
Private Sub Worksheet_Calculate2()
Dim MyResult As String
Application.EnableEvents = False
'first, unhide any rows that are currently hidden.
'This first part will hide the rows associated with the panels depending on number of panels generated by the product code
''''''''''''''''''''''''''''''''''''''''''''''''''
'change the name from Sheet1 to your worksheets name.
''''''''''''''''''''''''''''''''''''''''''''''''''
Rows("1:" & Worksheets("Cutting Sheet for Std Systems").UsedRange.Rows.Count).EntireRow.Hidden = False
'Cells(91,1) mean the cell in the 91 st row and the 1 st column, ie A91
'MyResult is the variable that takes it value.
''''''''''''''''''''''''''''''''''''''''''''''''''
'change the name from Sheet1 to your worksheets name.
MyResult = Worksheets("Cutting Sheet for Std Systems").Cells(6, 3).Value
''''''''''''''''''''''''''''''''''''''''''''''''''
'This is the spot where you'd add your other cases.
'Just keep adding your cases as in the examples. Be sure that all the cases are
'in between the Select and the End Select statements
Select Case MyResult
'Including the value 0 in case of a direct a = b type of thing; if you're confident that
'0 will never mean Empty String, remove it from the case
Case "XXP", "PXX", "PXXX", "XXXP"
Rows("36").EntireRow.Hidden = True
'Case Else is a special case that happens if none of the above values are
'detected in cell A91. It is optional.
'Case Else
'Rows("91:91").EntireRow.Hidden = True
End Select
'If you have any rows that should be always hidden, you can
' "rehide" any rows that you want always hidden.
'Rows("5:6").EntireRow.Hidden = True
Application.EnableEvents = True
End Sub