Multiple cells hiding rows, changing print area
I need a alteration to my program that will hide various rows and change
print area based on a separate cell than the current one. For example, if I type .84 into B20 I want it to adjust things as written below, but if I then type in JP into cell J20 I want it to hide past row 94 and print before that row. I tried doing this by setting up an all new sub for the worksheet in addition to this one (using this one as a pattern), but excel didn't like that. 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.9, 0.94, 0.76, 0.75, 0.78 Rows("63:93").EntireRow.Hidden = False Rows("94:124").EntireRow.Hidden = True ActiveSheet.PageSetup.PrintArea = "$A$1:I$93" Case 0.84 Rows("63:124").EntireRow.Hidden = True ActiveSheet.PageSetup.PrintArea = "$A$1:$I$62" Case Is < 0.75, Is 0.94, Is 0.79 < 0.84 Rows("63:124").EntireRow.Hidden = False ActiveSheet.PageSetup.PrintArea = "$A$1:$I$124" Case Is 0.84 < 0.9, Is 0.9 < 0.94 Rows("63:124").EntireRow.Hidden = False ActiveSheet.PageSetup.PrintArea = "$A$1:$I$124" End Select End Sub |
All times are GMT +1. The time now is 02:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com