Hi folks,

Hope someone can help with this printing query.

- I have a worksheet ("sheet1") which has 100 rows that
may contain data.

- Rows 1 to 40 are set to print at the top of every page.

- The page set up allows for rows 1:40 + another 15. So if
my print range was rows 1:70 then i'd get 2 pages, rows
1:100 = 4 pages etc.


Now i could easily set the macro to print range 1:100 but
if i only had data in rows 1:70 i'd have 2 pages printing
that i don't need. Not to mention the trees i'd be wasting.

I've been trying to find away that i can determine where
the data ends and selecting a print range accordingly. To
do this i thought about working out how many blank cells
there is in range A41:A100 (say it returns a value of 30)
storing that value in a cell (or could i calculate it in
the coding?) and then adding / deducting that value from
the fixed row numbers of 40 / 100.

I tried this but it doesn't seem to work ......

Set Blanks = Worksheets("sheet1").Cell("A40").Value
Set Row = "A1:I" & 100 - Blanks


Sheets("sheet1").Select
Range(Row).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1,
Collate:=True
End Sub



Can anyone help put me on the right track?

I'll bet it's really simple and i'm trying to make it
complicated !!!


TIA

Craig.