Dynamic Print Ranges
 

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.

Dynamic Print Ranges
 

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

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

--
Regards,
Tom Ogilvy

"Craig" wrote in message
...
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.

Dynamic Print Ranges
 

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


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

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

--
Regards,
Tom Ogilvy

"Craig" wrote in message
...
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.




.

Dynamic Print Ranges
 

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 )

--
Regards,
Tom Ogilvy



wrote in message
...
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


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

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

--
Regards,
Tom Ogilvy

"Craig" wrote in message
...
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.




.

Dynamic Print Ranges
 

Thanks Tom

Works a treat, much appreciated.


Cheers,

Craig.



-----Original Message-----
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 )

--
Regards,
Tom Ogilvy



wrote in message
...
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


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

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

--
Regards,
Tom Ogilvy

"Craig" wrote in message
...
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.




.



.