Printing of spreadsheet
Derrick
from a collection of spreadsheets (using formulas).
The formula to extract is about 2 pages long (about 120
rows). Usually it's a short list (within a page) but
sometimes it's long (>1 page). When the list ends, the
rest of cells will be filled with "".
Whenever I print spreadsheet, it will always print 2
pages, even if the second page is empty (filled with "").
Is there anyway I can configure so that excel only print
the second page only if there are values (i.e. extracted
data > 1 page long)?
Barry
Filling up the remaining space with "" shouldn't make a
difference. Are you filling the remainder with spaces?
If so, try using the TRIM function to weed out the spaces
at the end (i.e. put "=Trim(really long formula)" around
your current formula's).
Hope this is the solution you're looking for.
>.
>
Derrick
Thanks for the tip. Unfortunately it didn't work the way
I wanted.
When I did a print preview, the second empty page still
appears.
>.
>
Mike
only the visible data, because Excel sees the formulas as
part of the sheet even if the result is empty. If you
want only the first sheet, you have a couple of options.
Instead of clicking the Print button on the toolbar, press
Ctrl+P, or from the menu select File then Print. This
brings up a Print options screen. Under Print range,
click the Page(s) option, then select From 1 To 1.
Or you can create a custom macro to print the correct
sheets. This command will print out page 1 if the value
in cell B61 is empty, otherwise it will print 2 pages.
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=IIf(Range
("B61").Value <> "", 2, 1)
>.
>
Derrick
It works!!
Does this mean that I've to run the macro everytime I
want to print? Or can this be tied to the print function,
i.e. whenever ctrl+P the macro kicks in?
Thanks!!!
>.
>
Mike
the default shortcut to the print option when this
workbook is open (you could still acess the print option
from the menu if needed). To assign the shortcut, click
the play button (or Alt+F8) to get the list of macros.
Select the macro, click the Options button, then enter P
in the shortcut key box (Ctrl is implied). If the
workbook where this macro is stored is closed, then the
normal print option will run when you press Ctrl+P, so
there won't be any permanant changes to how Excel works.
>.
>
Derrick
Thanks!!
Dave Peterson
can pick out a column that always has data in it), is to apply
Data|Filter|autofilter.
Filter on non-blanks.
Print those visible cells.
========
Another way that's kind of neat:
Non-VBA method.
Select your worksheet (I'll call mine Sheet1.)
Pick a column that will always have data if that row has anything showing. I'm
gonna use column A.
Then Insert|Name|Define
In the Names in Workbook Box, type: sheet1!LastRow
In the refers to box, type this formula:
=MAX(ROW(sheet1!$A$1:$A$5000)*(sheet1!$A$1:$A$5000<>""))
adjust the rows to what you need
Now create another name for whole thing if all the rows had stuff that showed.
Call it sheet1!FullPrint
In my example, it looked like:
=sheet1!$A$1:$H$10000
Now one more range name, but this one is special. It's what excel uses for the
Print_Area.
The names box should look like this:
sheet1!Print_Area
the refers to box should be:
=offset(fullprint,0,0,lastrow)
The sheet1! stuff is important--just in case you want to do this on multiple
sheets.
The trick with the LastRow name is that it uses an array formula (but you don't
hit ctrl-shift-enter) to find the last "non-blank" (different than non-empty)
cell in that range (a1:a5000).
The offset() stuff says to take the fullprint range, starting at the topleftcell
of fullprint (that's the 0,0 portion) and resize it to the number of rows
calculated by LastRow.
this is a pretty neat technique. I found it (well close to it) in a template
that MS gives away.
It's used for loan repayment calculations:
Here's a loonnnnnnnnng link to it (all one line in your browser):
--
Dave Peterson
ec3...@msn.com