| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Not statements and blank page printing
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Not statements and blank page printing
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Not statements and blank page printing
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. "Charlie" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Not statements and blank page printing
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" "Charlie" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Print preview/page set-up is printing 100's of blank pages | Excel Discussion (Misc queries) | |||
| half page extra blank in Landscape mode PRINTING | Excel Discussion (Misc queries) | |||
| half page extra blank in Landscape mode PRINTING | Excel Discussion (Misc queries) | |||
| my excel document is printing blank pages after every page | Excel Discussion (Misc queries) | |||
| Stopping blank page printing | Charts and Charting in Excel | |||
Linear Mode
