Dynamic Print Ranges

C

Craig

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.
 
T

Tom Ogilvy

set rng = cells(rows.count,1).End(xlup)

Activesheet.PageSetup.Printarea = _
range("A41",rng).Resize(,9).Address(external:=True)
 
G

Guest

Hi Tom,

Thanks for the reply.

I should have said that cells A41:A100 on ("sheet1") have
formula which pulls data from another sheet if the cell
is <>""

This means it thinks the last used cell is A100 ... is
there away to incorporate an IsEmpty ?

Thanks again,

Craig
 
T

Tom Ogilvy

set rng = cells(rows.count,1).End(xlup)
do while trim(rng.Value) =""
set rng = rng.offset(-1,0)
Loop
Activesheet.PageSetup.Printarea = _
range("A41",rng).Resize(,9).Address(external:=True)
 
Top