I have the following code that unhides rows if a question is answered yes and leaves rows hidden if the question is answered no:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Address(0, 0) = "J147" And Target <> "" Then Rows("149:154").Hidden = False
If Target.Address(0, 0) = "J147" And Target = "" Then Rows("149:154").Hidden = True
If Target.Address(0, 0) = "J129" And Target <> "" Then Rows("130:139").Hidden = False
If Target.Address(0, 0) = "J129" And Target = "" Then Rows("130:139").Hidden = True
If Target.Address(0, 0) = "J62" And Target <> "" Then Rows("72:75").Hidden = False
If Target.Address(0, 0) = "J62" And Target = "" Then Rows("72:75").Hidden = True
Application.EnableEvents = True
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, [J:J]) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target <> "" Then Rows(Target.Row + 1 & ":" & Target.Row + 2).Hidden = False
If Target.Address(0, 0) = "J14" And Target = "" Then Rows("15:16").Hidden = True
If Target.Address(0, 0) = "J18" And Target = "" Then Rows("19:20").Hidden = True
If Target.Address(0, 0) = "J23" And Target = "" Then Rows("24:25").Hidden = True
If Target.Address(0, 0) = "J28" And Target = "" Then Rows("29:30").Hidden = True
If Target.Address(0, 0) = "J32" And Target = "" Then Rows("33:34").Hidden = True
If Target.Address(0, 0) = "J36" And Target = "" Then Rows("37:38").Hidden = True
If Target.Address(0, 0) = "J54" And Target = "" Then Rows("55:56").Hidden = True
If Target.Address(0, 0) = "J58" And Target = "" Then Rows("59:60").Hidden = True
If Target.Address(0, 0) = "J78" And Target = "" Then Rows("79:80").Hidden = True
If Target.Address(0, 0) = "J83" And Target = "" Then Rows("84:85").Hidden = True
If Target.Address(0, 0) = "J88" And Target = "" Then Rows("89:90").Hidden = True
If Target.Address(0, 0) = "J95" And Target = "" Then Rows("96:97").Hidden = True
If Target.Address(0, 0) = "J102" And Target = "" Then Rows("103:104").Hidden = True
If Target.Address(0, 0) = "J114" And Target = "" Then Rows("115:116").Hidden = True
If Target.Address(0, 0) = "J118" And Target = "" Then Rows("119:120").Hidden = True
If Target.Address(0, 0) = "J125" And Target = "" Then Rows("126:127").Hidden = True
If Target.Address(0, 0) = "J141" And Target = "" Then Rows("142:143").Hidden = True
Application.EnableEvents = True
End Sub
the code is working great, however, I would like it to automatically adjust the page setup so that when I print, it will print everything in two pages regardless of how many rows are hidden or unhidden.
Any help would be appreciated.
thanks
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Address(0, 0) = "J147" And Target <> "" Then Rows("149:154").Hidden = False
If Target.Address(0, 0) = "J147" And Target = "" Then Rows("149:154").Hidden = True
If Target.Address(0, 0) = "J129" And Target <> "" Then Rows("130:139").Hidden = False
If Target.Address(0, 0) = "J129" And Target = "" Then Rows("130:139").Hidden = True
If Target.Address(0, 0) = "J62" And Target <> "" Then Rows("72:75").Hidden = False
If Target.Address(0, 0) = "J62" And Target = "" Then Rows("72:75").Hidden = True
Application.EnableEvents = True
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, [J:J]) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target <> "" Then Rows(Target.Row + 1 & ":" & Target.Row + 2).Hidden = False
If Target.Address(0, 0) = "J14" And Target = "" Then Rows("15:16").Hidden = True
If Target.Address(0, 0) = "J18" And Target = "" Then Rows("19:20").Hidden = True
If Target.Address(0, 0) = "J23" And Target = "" Then Rows("24:25").Hidden = True
If Target.Address(0, 0) = "J28" And Target = "" Then Rows("29:30").Hidden = True
If Target.Address(0, 0) = "J32" And Target = "" Then Rows("33:34").Hidden = True
If Target.Address(0, 0) = "J36" And Target = "" Then Rows("37:38").Hidden = True
If Target.Address(0, 0) = "J54" And Target = "" Then Rows("55:56").Hidden = True
If Target.Address(0, 0) = "J58" And Target = "" Then Rows("59:60").Hidden = True
If Target.Address(0, 0) = "J78" And Target = "" Then Rows("79:80").Hidden = True
If Target.Address(0, 0) = "J83" And Target = "" Then Rows("84:85").Hidden = True
If Target.Address(0, 0) = "J88" And Target = "" Then Rows("89:90").Hidden = True
If Target.Address(0, 0) = "J95" And Target = "" Then Rows("96:97").Hidden = True
If Target.Address(0, 0) = "J102" And Target = "" Then Rows("103:104").Hidden = True
If Target.Address(0, 0) = "J114" And Target = "" Then Rows("115:116").Hidden = True
If Target.Address(0, 0) = "J118" And Target = "" Then Rows("119:120").Hidden = True
If Target.Address(0, 0) = "J125" And Target = "" Then Rows("126:127").Hidden = True
If Target.Address(0, 0) = "J141" And Target = "" Then Rows("142:143").Hidden = True
Application.EnableEvents = True
End Sub
the code is working great, however, I would like it to automatically adjust the page setup so that when I print, it will print everything in two pages regardless of how many rows are hidden or unhidden.
Any help would be appreciated.
thanks