Hide Rows based if column I = 0, then set print area from row 80 to everything visibl

[cbs81]

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






[VBA]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
[/VBA]

[p45cal]

Are you sure that this line:

Set rng = Range(Cells(80, 1), _ 
    Cells(Cells(325, 2).End(xlDown).Row, 9))

isn't making rng go right down to the bottom of the sheet?
test it with a few

MsgBox rng.Address

in the code.

p45cal

[Charlize]

How I'm doing this.
1. Define the printarea for all the data.
2. Hide the rows that you don't want.
3. Do a printpreview and there you go. Only the rows that you want.