Hi,

You should use a case select instead of IF statement.
I group 1.4 and .98 in the case statment.
Then you can pass multiple region for the Print area, separated by
comma.
Like so: "$A$1:$I$155, $A$187:$I$217"

Try this:

Sub Worksheet_Change(ByVal Target As Range)
Dim v As Double

Set t = Target
Set r = Range("B20")
If Intersect(t, r) Is Nothing Then
Exit Sub
Else
v = Val(r.Value)
End If

Select Case v

Case 0.98, 1.4
Rows("156:186").EntireRow.Hidden = False
Rows("187:217").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:I$186"
Case 0.76
Rows("187:217").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$155, $A$187:$I$217"
Rows("156:186").EntireRow.Hidden = True
Case 0
Rows("156:217").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$155"

End Select
End Sub



Charlie
'Opener Consulting Home' (http://www.openerconsulting.com)


timmtamm;180224 Wrote:
I need to make it so that if the value does not equal .76, .98, or 1.4,
it
will return
the spreadsheet to the final result of hiding rows 156:217. Further, if
there
is an easy way to combine .98 and 1.4 (as the end result is the same),
how
can that be done.

Also, with this code, if the value of the cell is .76 and I then
print,
excel will also print a blank sheet in place of the rows that are
hidden.
How do I print that final page without printing the hidden rows page
(which
is blank because it is hidden)

Here is the full code.

Private Sub Worksheet_Change(ByVal Target As Range)
Set t = Target
Set r = Range("B20")
If Intersect(t, r) Is Nothing Then
Exit Sub
Else: v = r.Value
If v = 0.98 Then
Rows("156:186").EntireRow.Hidden = False
Rows("187:217").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$186"
End If
If v = 1.4 Then
Rows("156:186").EntireRow.Hidden = False
Rows("187:217").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$186"
End If
If v = 0.76 Then
Rows("187:217").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$217"
Rows("156:186").EntireRow.Hidden = True
End If
If v = "" Then
Rows("156:217").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$155"
End If
End If
End Sub


--
Charlie
------------------------------------------------------------------------
Charlie's Profile: http://www.thecodecage.com/forumz/member.php?userid=89
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=49846