Private Sub Hide/unhide-compile error: procedure too large response

[cairo95]

Greetings,

I am working on a spreadsheet for risk assessment entries, the spreadsheet will have specified areas for up to 20 hazards with 12 risks for each hazard.
I will need specified rows to hide or unhide based on the numeric value of a cell in column A. i have received excellent help and was able to get some functionality working in Private Sub Worksheet_Change (ByValue Target as Range) but now i am getting a compile error: procedure too large response.

Is there a way to streamline the below code with private sub?

All triggers will be in column A

Starting in column A row 50 (the default is Row 50:53, 56:59, 71:74 will always be unhidden)
Each incident will have 1 Hazard and a max of 12 Risks for a max of 20 hazards with 12 risks each.

Therefore:

If row 50 = 1 then (this is hazard 1)
Unhide rows 97:100, 113:116, 128:131 (unhide hazard 2)
Else hide rows 97:142 (if not then hide hazard 2)
End if

If row 50 = 1 and row 53 =1 then (when 1 is entered in row 53 (risk 1) this unhides the row below risk 1 (54) to allow entry for risk 2 if needed and so on; up to risk 12 if needed.
Unhide rows 54, 69, 84 (unhide risk 2)
End if
Elseif row 50 = 1 and row 54 =2 then
Unhide rows 55, 70, 85 (unhide risk 3)
End if
Elseif row 50 = 1 and row 55 =3 then
Unhide rows 56, 71, 86 (unhide risk 4)
End if
Elseif row 50 = 1 and row 56 =4 then
Unhide rows 57, 72, 87 (unhide risk 5)
End if
Elseif row 50 = 1 and row 57 =5 then
Unhide rows 58, 73, 88 (unhide risk 6)
End if
Elseif row 50 = 1 and row 58 =6 then
Unhide rows 59, 74, 89 (unhide risk 7)
End if
Elseif row 50 = 1 and row 59 =7 then
Unhide rows 60, 75, 90 (unhide risk 8)
End if
Elseif row 50 = 1 and row 60 =8 then
Unhide rows 61, 76, 91(unhide risk 9)
End if
Elseif row 50 = 1 and row 61 =9 then
Unhide rows 62, 77, 92 (unhide risk 10)
End if
Elseif row 50 = 1 and row 62 =10 then
Unhide rows 63, 78, 93 (unhide risk 11)
End if
Elseif row 50 = 1 and row 63 =11 then
Unhide rows 64, 79, 94 (unhide risk 12)
End if
Elseif row 50 = 1 and row 64 =12 then
Unhide rows 65, 80, 95

The above is hazard 1 with potential for 12 risk entries. This will need to be repeated for up to the 20th Hazard. The pattern here is each Hazard begins every 47th row after the 50th row. The same with the risks…so the next hazard will begin in row 97..etc.
Your help will be greatly appreciated!

thanks in advance!
Cairo95

 

[Fluff]

Glad to help & thanks for the feedback

 

[cairo95]

Hello Fluff,

sorry to bother you again but i have another questions about the same code you helped me with. So if cell A50 is not blank the specified cells are unhidden, ie.


Range("97:101,113:116,128:131").EntireRow.Hidden = IIf(Target = "", True, False), i see that no action is taken if false, so how would i hide cells 97:142 if A50 is blank?

 If Not Intersect(Target, Range("A50")) Is Nothing Then
      Range("97:101,113:116,128:131").EntireRow.Hidden = IIf(Target = "", True, False)
    End If [CODE]

below is a snippet for you to see.

[CODE]Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   Application.ScreenUpdating = False
   Application.EnableEvents = True
   
  'risk 2-12 triggerd by prior Risk for Hazard 1

   If Not Intersect(Target, Range("$A53:A$64")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)
   
     
      Range("70:70,85:85").EntireRow.Hidden = Range("A54").Value = ""
      Range("71:71,86:86").EntireRow.Hidden = Range("A55").Value = ""
      Range("72:72,87:87").EntireRow.Hidden = Range("A56").Value = ""
      Range("73:73,88:88").EntireRow.Hidden = Range("A57").Value = ""
      Range("74:74,89:89").EntireRow.Hidden = Range("A58").Value = ""
      Range("75:75,90:90").EntireRow.Hidden = Range("A59").Value = ""
      Range("76:76,91:91").EntireRow.Hidden = Range("A60").Value = ""
      Range("77:77,92:92").EntireRow.Hidden = Range("A61").Value = ""
      Range("78:78,93:93").EntireRow.Hidden = Range("A62").Value = ""
      Range("79:79,94:94").EntireRow.Hidden = Range("A63").Value = ""
      Range("80:80,95:95").EntireRow.Hidden = Range("A64").Value = ""
      
  
   End If
 
'if A50 has a value then unhide input cells haz 2

   If Not Intersect(Target, Range("A50")) Is Nothing Then
      Range("97:101,113:116,128:131").EntireRow.Hidden = IIf(Target = "", True, False)
    End If

'Hazard 2--------------------------------------------------------------------
    
  'risk 2-12 triggerd by Risk 1 for Hazard 2
  
   If Not Intersect(Target, Range("$A100:A$111")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)

      Range("117:117,132:132").EntireRow.Hidden = Range("A101").Value = ""
      Range("118:118,133:133").EntireRow.Hidden = Range("A102").Value = ""
      Range("119:119,134:134").EntireRow.Hidden = Range("A103").Value = ""
      Range("120:120,135:135").EntireRow.Hidden = Range("A104").Value = ""
      Range("121:121,136:136").EntireRow.Hidden = Range("A105").Value = ""
      Range("122:122,137:137").EntireRow.Hidden = Range("A106").Value = ""
      Range("123:123,138:138").EntireRow.Hidden = Range("A107").Value = ""
      Range("124:124,139:139").EntireRow.Hidden = Range("A108").Value = ""
      Range("125:125,140:140").EntireRow.Hidden = Range("A109").Value = ""
      Range("126:126,141:141").EntireRow.Hidden = Range("A110").Value = ""
      Range("127:127,142:142").EntireRow.Hidden = Range("A111").Value = ""
     
   
   End If
'if A97 has a value then unhide input cells haz 3

      
   If Not Intersect(Target, Range("A97")) Is Nothing Then
        Range("144:148,160:163,175:178").EntireRow.Hidden = IIf(Target = "", True, False)
        
   
    End If
      
      

'Hazard 3------------------------------------------------------------------
    'risk 2-12 triggerd by prior Risk for Hazard 3

   If Not Intersect(Target, Range("$A147:A$158")) Is Nothing Then
      Target.Offset(1).EntireRow.Hidden = IIf(Target = "", True, False)

      Range("164:164,179:179").EntireRow.Hidden = Range("A148").Value = ""
      Range("165:165,180:180").EntireRow.Hidden = Range("A149").Value = ""
      Range("166:166,181:181").EntireRow.Hidden = Range("A150").Value = ""
      Range("167:167,182:182").EntireRow.Hidden = Range("A151").Value = ""
      Range("168:168,183:183").EntireRow.Hidden = Range("A152").Value = ""
      Range("169:169,184:184").EntireRow.Hidden = Range("A153").Value = ""
      Range("170:170,185:185").EntireRow.Hidden = Range("A154").Value = ""
      Range("171:171,186:186").EntireRow.Hidden = Range("A155").Value = ""
      Range("172:172,187:187").EntireRow.Hidden = Range("A156").Value = ""
      Range("173:173,188:188").EntireRow.Hidden = Range("A157").Value = ""
      Range("174:174,189:189").EntireRow.Hidden = Range("A158").Value = ""
   
   End If

Thanks again!

Cairo9544

 

[Fluff]

That should happen automatically if you delete the value in A50

 

[cairo95]

i dont see how it could since there is no code requiring a certain range of rows to hide. there are 2 different requirements when cell A50 is not blank then unhide rows "97:101 and 113:116 and 128:131" if it is blank then hide rows "97:142"

thanks

Cairo9544

 

[Fluff]

Missed the different rows.
Try

If Not Intersect(Target, Range("A50")) Is Nothing Then
   Range(IIf(Target.Value = "", "97:142", "97:101,113:116,128:131")).EntireRow.Hidden = IIf(Target.Value = "", True, False)
End If

 

[cairo95]

perfect! thanks again!

Best Regards,

Cairo95

 

[Fluff]

You're welcome

 

[cairo95]

my new form is a workof art thanks to you!