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

[DIGBY67]

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

 

[DRJ]

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.

 

[DIGBY67]

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

 

[DIGBY67]

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

 

[tactps]

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.

 

[Yogi Anand]

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.

 

[tactps]

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

 

[DIGBY67]

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

 

[tactps]

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

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

 

[Barry Katcher]

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.

Book1
	A	B	C	D
1	print	Slip1	xxxx	50
2	print	yyy	yyyy	yyyyy
3	print	zzz	zzzz	zzzzz
4	print	Slip2	xxxx	100
5	print	yyy	yyyy	yyyyy
6	print	zzz	zzzz	zzzzz
7	hide
8	hide
9	hide
10				150
Sheet2