Excel BLANK PAGES-I don't want to print em-macro needed? from Mr Excel


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

BLANK PAGES-I don't want to print em-macro needed?
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

DIGBY67
Welcome to the Board


Joined: 13 Apr 2004
Posts: 5
Location: RALEIGH, NC
Flag: Usa

Status: Offline

 Reply with quote  

BLANK PAGES-I don't want to print em-macro needed?

SCENERIO:
I've created a one worksheet document that has 5 pages, one below the next (I used page breaks).
At the bottom of each page is a cell that holds an "if/and" argument designed to display a blank ("") in that cell if there is no other data on that particular page (the entire page will be blank).
The worksheet also contains a macro that turns the "fixed decimal" option on/off when the sheet opens/closes, so decimals are automatically entered (258 displays 2.58).
Everything works like a champ, but...

PROBLEM:
When I click the printer icon, the printer prints all 5 pages, even the ones left blank.
The users won't be able to use any kind of print set up...they'll just click the "print" icon.

I WANT:
I want the printer to ignore the blank pages.
I don't want to do anything but click the "print" icon.
Any macro has to turn itself on and off when the sheet opens and closes so it won't affect other excel documents.

An answer will bring together this little project that I designed to shave about 20 minutes of grunt work off of my coworker's day.
Thanks in advance for any help.
Digby67

Post Tue Apr 13, 2004 1:33 am 
 View user's profile Send private message

DRJ
Board Master
Board Master


Joined: 18 Feb 2002
Posts: 3193
Location: California
Flag: Usa

Status: Offline

 Reply with quote  

Hi - Welcome to the board

In the before_print event use the printout option to printout the specific pages.

You can use an if statement to determine what pages to print and then just use printout.
_________________







-Jacob

Post Tue Apr 13, 2004 1:36 am 
 View user's profile Send private message Send e-mail

DIGBY67
Welcome to the Board


Joined: 13 Apr 2004
Posts: 5
Location: RALEIGH, NC
Flag: Usa

Status: Offline

 Reply with quote  

Re: BLANK PAGES-I don't want to print em-macro needed?

DrJ-thanks for the reply.
What I really want to do is set up the sheet so all anyone will have to do is just click "print" without having to select the pages, if that's possible.
My end users might have limited experience with excel, so I'd like to make it as simple as I can.
Digby

Post Tue Apr 13, 2004 1:56 am 
 View user's profile Send private message

DIGBY67
Welcome to the Board


Joined: 13 Apr 2004
Posts: 5
Location: RALEIGH, NC
Flag: Usa

Status: Offline

 Reply with quote  

Re: BLANK PAGES-I don't want to print em-macro needed?

DrJ.
I might have misunderstood you.
I'm obviously not very experienced at this either.
Are you talking about selecting the pages to print after you've selected print from the file...print...options...page?
Or are you talking about something that I'm writing like a macro?
Can you help me out a little more if you're talking about writing something into the worksheet?
Thanks.
digby

Post Tue Apr 13, 2004 2:02 am 
 View user's profile Send private message

tactps
Board Master


Joined: 20 Jan 2004
Posts: 664
Location: Melbourne
Flag: Australia

Status: Offline

 Reply with quote  

I think you want a macro that will change the print area to be printed. Can you advise where the cells are that show ""?

If so, we can update the macro so that it checks each one until it finds the first blank one and then sets the print area accordingly.
_________________
Why do 24-hour 7-11's still have a lock on the door?

Post Tue Apr 13, 2004 2:09 am 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 6201
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: BLANK PAGES-I don't want to print em-macro needed?

Hi DIGBY67:

In line with the suggestion by tactps regarding setting Print Area, to print contiguously, you will need to hide the rows of the page that is blank -- and that can be done using a macro.
_________________
Regards!
Yogi Anand

Post Tue Apr 13, 2004 2:18 am 
 View user's profile Send private message

tactps
Board Master


Joined: 20 Jan 2004
Posts: 664
Location: Melbourne
Flag: Australia

Status: Offline

 Reply with quote  

To explain further, say the cell with the formula is every 50 rows in column E, and you want to print columns A to G:

Sub Macro4()
Dim counter As Integer
Range("E50").Select
counter = 100
PrintRow = ActiveCell.Row
Do Until ActiveCell.Text = ""
Range("E" & counter).Select
PrintRow = ActiveCell.Row - 50
counter = counter + 50
Loop
ActiveSheet.PageSetup.PrintArea = "$A$1:$G$" & PrintRow
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub
_________________
Why do 24-hour 7-11's still have a lock on the door?

Post Tue Apr 13, 2004 2:19 am 
 View user's profile Send private message

DIGBY67
Welcome to the Board


Joined: 13 Apr 2004
Posts: 5
Location: RALEIGH, NC
Flag: Usa

Status: Offline

 Reply with quote  

Re: BLANK PAGES-I don't want to print em-macro needed?

Each page is 2 columns by 33 rows.
Each page is really a bank deposit ticket with a potential total on every 33rd line of column B.
If the first row of a page has an entry (ticket started) but the first row of the next page is blank (don't need the next ticket) then the last cell on the used page (deposit ticket) prints a total. You're done.
However, if the first line on the following page has an entry (you needed another deposit slip to continue listing your checks) then the last cell of the deposit ticket you just filled up displays "see next" on the last line.
Finally, if the first line on a particular page (ticket) is blank (you didn't need that deposit slip) then the last line on that slip displays blank ("").
SO...
I can't really let the print area end on the first blank row it gets to, because there will probably be blank rows between the end of the entries and the total at the bottom on the last deposit ticket she needed.
I'd need something that would say "only print everything above row 33 if 34 is blank...if 34 isn't blank, then print everything above row 63 if 64 is blank...if 64 isn't blank, then...etc."
I hope this helps clarify.
Any hope?
digby

Post Tue Apr 13, 2004 3:27 am 
 View user's profile Send private message

tactps
Board Master


Joined: 20 Jan 2004
Posts: 664
Location: Melbourne
Flag: Australia

Status: Offline

 Reply with quote  

Re: BLANK PAGES-I don't want to print em-macro needed?

To adjust my code (if I read it correctly):

code:
Sub Macro4()
Dim counter As Integer
     Range("B33").Select
     counter = 66
     PrintRow = ActiveCell.Row
     Do Until ActiveCell.Offset(1, 0).Text = ""
     Range("B" & counter).Select
     PrintRow = ActiveCell.Row - 33
     counter = counter + 33
     Loop
     ActiveSheet.PageSetup.PrintArea = "$A$1:$G$" & PrintRow
     ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub




If this doesn't work, then you should post some samples and it will probably make more sense to me.

I am assuming that once it hits a blank, then it should stop (you don't have a slip completed, then a blank, then another completed).
icon_biggrin.gif
_________________
Why do 24-hour 7-11's still have a lock on the door?

Post Tue Apr 13, 2004 4:07 am 
 View user's profile Send private message

Barry Katcher
Board Master
Board Master


Joined: 26 Feb 2002
Posts: 2146
Location: Florida. Yeaahh!
Flag: Usa

Status: Offline

 Reply with quote  

Re: BLANK PAGES-I don't want to print em-macro needed?

Use a formula similar to that in column A of the sample below. Then, record a macro to AutoFilter on column A, using "show" as the filter. Place the code in the sheet module, in a Before_Print macro. You'll also have to record a macro to "Show all" and place it in an After_print macro.

To avoid using two macros, record as above to filter, then to print the range, then to unfilter, and place it in a standard module. Then assign it to a giant button on top of the sheet, with "PRINT" in 48 point bold font, so the dummy end-users can perceive that this is how one prints this sheet. Works for me and all my dummies.

Microsoft Excel - Book1___Running: xl2000 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
1
printSlip 1xxxx50
2
printyyyyyyyyyyyy
3
printzzzzzzzzzzzz
4
printSlip 2xxxx100
5
printyyyyyyyyyyyy
6
printzzzzzzzzzzzz
7
hide   
8
hide   
9
hide   
10
   150
Sheet2 

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

_________________
Barry-I used to be a werewolf, but I'm much better nowWWWWWWW!

Post Tue Apr 13, 2004 4:34 am 
 View user's profile Send private message Send e-mail AIM Address
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.