Not statements and blank page printing

[timmtamm]

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]

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]

Thanks Charlie,
that does solve the problem with the page printing.

One thing remains. It's soooo close to where I want it. I can then change
the last case statement to this:

Case 0, Is < 0.76, Is > 1.4
Rows("156:217").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$155"

so if the value is less than .76 or greater than 1.4, it will react like 0
or nothing, but I don't know how to do the range between .76 and .98, and
between .98 and 1.4.

 

[timmtamm]

Never mind, I think I've finally got it. I had to make another case
statement to do it, but it works. Thank you so much.

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