cbs81
07-20-2007, 12:03 AM
Hi everyone,
I have written some code below.. it works but when the print preview page comes up it says page 1 of 2000 pages... when there is really only one page of data after all other rows have been hidden.
Ok heres what im trying to do... I have a column that has either a value or is 0. If it is 0 I want all rows hidden from row 80 to row 325. Then I want to set a print area from whatever is visible Only from row A80 to IXXX.. XXX is up to the rows that are visible.
Say there is data in I80 to I120. All rows from I120 onwards will be hidden and the print area and print preview will be from A80 to I120.
How can we do this without it saying page 1 of 2000 on the print preview page......??
Thankyou so much
Sub FieldDetermined()
Dim rng As Range
Set rng = Range(Cells(80, 1), _
Cells(Cells(325, 2).End(xlDown).Row, 9))
Application.Run "HURows1"
ActiveSheet.PageSetup.PrintArea = rng.Address
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.Orientation = xlPortrait
ActiveSheet.PrintPreview
End Sub
Sub HURows1()
'
Dim RowCnt As Long
'
Const BeginRow As Long = 82
Const Endrow As Long = 324
Const ChkCol As Long = 9
'
With Worksheets("Date Summary")
.Unprotect Password:="cbs"
'
For RowCnt = BeginRow To Endrow
If .Cells(RowCnt, ChkCol).Value = 0 Then
.Rows(RowCnt).Hidden = True
Else
.Rows(RowCnt).Hidden = False
End If
Next RowCnt
'
.Protect Password:="cbs"
End With
End Sub
I have written some code below.. it works but when the print preview page comes up it says page 1 of 2000 pages... when there is really only one page of data after all other rows have been hidden.
Ok heres what im trying to do... I have a column that has either a value or is 0. If it is 0 I want all rows hidden from row 80 to row 325. Then I want to set a print area from whatever is visible Only from row A80 to IXXX.. XXX is up to the rows that are visible.
Say there is data in I80 to I120. All rows from I120 onwards will be hidden and the print area and print preview will be from A80 to I120.
How can we do this without it saying page 1 of 2000 on the print preview page......??
Thankyou so much
Sub FieldDetermined()
Dim rng As Range
Set rng = Range(Cells(80, 1), _
Cells(Cells(325, 2).End(xlDown).Row, 9))
Application.Run "HURows1"
ActiveSheet.PageSetup.PrintArea = rng.Address
ActiveSheet.PageSetup.FitToPagesWide = 1
ActiveSheet.PageSetup.Orientation = xlPortrait
ActiveSheet.PrintPreview
End Sub
Sub HURows1()
'
Dim RowCnt As Long
'
Const BeginRow As Long = 82
Const Endrow As Long = 324
Const ChkCol As Long = 9
'
With Worksheets("Date Summary")
.Unprotect Password:="cbs"
'
For RowCnt = BeginRow To Endrow
If .Cells(RowCnt, ChkCol).Value = 0 Then
.Rows(RowCnt).Hidden = True
Else
.Rows(RowCnt).Hidden = False
End If
Next RowCnt
'
.Protect Password:="cbs"
End With
End Sub