I have the following code that unhides rows if the answer to a series of questions is yes and leaves them hidden if the answer is 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 works great, however, the problem is that once rows are unhidden, the page set up gets screwy and prints to more than two pages. Does anyone know how to tweek it so that it will always print to exactly two pages no matter how many rows are hidden/unhidden?
thanks for the help
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 works great, however, the problem is that once rows are unhidden, the page set up gets screwy and prints to more than two pages. Does anyone know how to tweek it so that it will always print to exactly two pages no matter how many rows are hidden/unhidden?
thanks for the help