My workbook is about 5 pages long. Each page contains column
headers at the top. If a users fills up the rows of the first two
pages, and presses print, Excel will print all 5 pages. But, really
only the first two pages needed to be printed.

What are some good methods to force Excel to only print pages
that contain "data"?

My workbook is about 5 pages long. Each page contains column
headers at the top. If a users fills up the rows of the first two
pages, and presses print, Excel will print all 5 pages. But, really
only the first two pages needed to be printed.

What are some good methods to force Excel to only print pages
that contain "data"?

I hook the Print icon to display the Prit dialog in all cases,
specifically so I can choose/set what gets prited.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

"GS" wrote:

I hook the Print icon to display the Prit dialog in all cases,
specifically so I can choose/set what gets prited.


How do you "hook" the Print icon?

I was also looking for an automated solution that just
prints pages that actually contain data, just to make it
easier for users.

"GS" wrote:

I hook the Print icon to display the Prit dialog in all cases,
specifically so I can choose/set what gets prited.


How do you "hook" the Print icon?

You assign your own 'OnAction' to it, OR replace the Standard toolbar
icon with the 'Print...' icon via customize. In v2007+ you need to
assign your own 'OnCtion' via xml in a later file format of
Personal.xls!

I was also looking for an automated solution that just
prints pages that actually contain data, just to make it
easier for users.

This is possible when you assign your own 'OnAction' so Excel uses your
macro to print conditional according to your criteria.

Otherwise, if this is project-based then add the necessary code to make
changes at project startup and restore them at project shutdown!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Typo...

In v2007+ you need to assign your own 'OnAction' via xml in a later
file format of Personal.xls!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Robert,

Am Sat, 8 Nov 2014 12:45:07 -0700 schrieb Robert Crandal:

What are some good methods to force Excel to only print pages
that contain "data"?

insert following code in the codemodule of "ThisWorkbook" and modify
sheet name and last column:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim LRow As Long

With ActiveSheet
'Modify sheet name
If .Name < "Sheet1" Then Exit Sub
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
With .PageSetup
'Modify last column
.PrintArea = "$A$1:$G$" & LRow
End With
End With
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

"Claus Busch" wrote:

With ActiveSheet
'Modify sheet name
If .Name < "Sheet1" Then Exit Sub
LRow = .Cells(Rows.Count, 1).End(xlUp).Row
With .PageSetup
'Modify last column
.PrintArea = "$A$1:$G$" & LRow
End With
End With
End Sub


Thanks for your solutions, Claus and Gary (GS), but....
I might want a different solution. Let me clarify a bit.

Users can only enter data into the following ranges:
A5:N20, A30:N50, A60:N80, and A90:N100.

If only the first two ranges contain data, and the user
presses Print, then I want to automatically print the first
two pages (or ranges), without the user having to choose
which pages to print.

I hope that is more clear.

See if this scenario will work for you...

I have an app called QSP which I made for a client so their sales reps
could process quotes, sales orders, and purchase orders for their
products using a single product template. The template[s] list every
conceivable option/configuration for each product, but rarely was all
these selected. The calcs on the sheets were based on a value entered
for 'Qty' (the 1st input field on a line item), and 'Unit Price'. What
got printed was determined by filtering on a helper col that flagged
'P' via formula if a line item was selected based on 'Qty' and/or
'Amount' not being zero.

The print routine only printed rows with the flag. Obviously, all the
rows to print by default were flagged with a constant value so only the
optional stuff used the IF() function.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Robert,

Am Wed, 12 Nov 2014 13:15:28 -0700 schrieb Robert Crandal:

Users can only enter data into the following ranges:
A5:N20, A30:N50, A60:N80, and A90:N100.

where are the headers? What range is printarea?


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Robert,

Am Wed, 12 Nov 2014 13:15:28 -0700 schrieb Robert Crandal:

Users can only enter data into the following ranges:
A5:N20, A30:N50, A60:N80, and A90:N100.

then try:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long, Counter As Long
Dim strRng As String
Dim varRng As Variant

strRng = "A5:N20,A30:N50,A60:N80,A90:N100"
varRng = Split(strRng, ",")

With ActiveSheet
If .Name < "Sheet1" Then Exit Sub

With .PageSetup
.Orientation = xlLandscape
.PrintArea = "$A$1:$N$110"
End With

For i = LBound(varRng) To UBound(varRng)
If WorksheetFunction.CountA(.Range(varRng(i))) 0 Then
Counter = Counter + 1
Else
Exit For
End If
Next
.PrintOut from:=1, to:=Counter
End With
End Sub



Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

On Saturday, November 8, 2014 1:44:19 PM UTC-6, Robert Crandal wrote:
My workbook is about 5 pages long. Each page contains column
headers at the top. If a users fills up the rows of the first two
pages, and presses print, Excel will print all 5 pages. But, really
only the first two pages needed to be printed.

What are some good methods to force Excel to only print pages
that contain "data"?

How about just hiding the rows you don't want to printprintunhide

On Saturday, November 8, 2014 1:44:19 PM UTC-6, Robert Crandal wrote:
My workbook is about 5 pages long. Each page contains column
headers at the top. If a users fills up the rows of the first two
pages, and presses print, Excel will print all 5 pages. But, really
only the first two pages needed to be printed.

What are some good methods to force Excel to only print pages
that contain "data"?

How about just hiding the rows you don't want to printprintunhide

That's essentially what my suggestion does so only the 'flagged' rows
are visible before doing PrintOut! This is a toggle so users can
auto-reset to show either 'All' items or just 'Selected' items. The
'PrintSelected' routine hides unflagged rows and so is why I also have
a toggle menu since printing may not always be only selected items if
the customer wants a complete list of the entire product's options.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion