Printing only Rows with Visible Data

[Guest]

Hello. My question is, let's say I have columns A-B unprotected, so users can
drop the current month's report. Column C is protected and has an if formula
to check if A is empty, " ", else to get B less A. The report that will be
dropped every month on Columns A-B may be longer (in rows) or shorter than
the previous month. So I copy-pasted the formula in Column C down to C200,
which is just an estimate. With this template, I did a print and it's
actually printing up to cell C200, I'm assuming because there is a formula in
there. What I am hoping to do is to print only the rows with visible data.
So if Col A-B is only up to row 50, just to print up to that row. Without
having to select a print area everytime, is there a way to automate the
process that when I am printing the report only the visible data will be
printed?

thank you.

 

[Guest]

Your data should automatically only print visible data....do you have
formatting in the cells that are empty that are printing? ie...are your
cells in color or have an outline? if they do, they will print regardless of
data...so, just unformat them and you shoud be good to go.

Hopefully helpful,
Carla Wood

 

[Guest]

Hi Carla, thanks for the response. Nope, there is not formatting in the
cell. If you try like just keying anything on cell b5. then scroll down to
c200 and just type in the formula of "=if(isblank(a200),"",b200-a200)".
since there's no data in a200, c200 will bring back a value of " ". then try
print preview, it'll give you more than one page to print, which I'm guessing
is because you've got a formula on C200.

Any help?

 

[Dave Peterson]

First, just to make life easier, I hope you're actually returning "" (no space
included--not " ").

Second, you have a couple of choices.

#1.

Apply Data|Filter|autofilter to that column.
Show the non-blanks
Print that and then
Data|Filter|Show all (to see everything again)

But it sounds like the worksheet is protected.

If you already have the outline applied, you can protect the worksheet in code
(auto_open/workbook_open??).

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Protect Password:="hi", userinterfaceonly:=True
.EnableOutlining = True
.EnableAutoFilter = True
End With
End Sub

It needs to be reset each time you open the workbook. (excel doesn't remember
it after closing the workbook.)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


#2.

Manually change the print range via:
select the range to print
file|print area|set print area

#3.

Just print what you select
Select the range to print
file|print
choose Selection

#4.

Have excel adjust the print range for you:

I'm gonna use column C as a column that always has something in it if that row
should print. You can change that if your formula is in a different column.

Insert|Name|Define
Names in workbook: Sheet1!LastRow
Use this formula
Refers to: =LOOKUP(2,1/(Sheet1!$C$1:$C$2000<>""),ROW(Sheet1!$C$1:$C$2000))

(Make that 2000 big enough to extend past the last possible row--but don't use
the whole column.)

Then once more:
Insert|Name|Define
Names in workbook: Sheet1!Print_Area
Use this formula
Refers to: =OFFSET(Sheet1!$A$1,0,0,lastRow,5)

That last 5 represents the last column to print (A:E). Change it to what you
want.

And change the worksheet (sheet1) if necessary (in all the places).

 

[Guest]

Well maybe....it page 2 on the print preview blank or does it have something

 

[Guest]

Based on page margins of (in inches): Top & Bottom at 1 and left and right at
..75, there are 4 pages in print preview. Pages 2 to 4 are blank, because
there is no visible data but that one formula in cell c200.

Without having to set print area everytime, how can I tell Excel to just
print based on rows with visible data and not cells that are non blank
because they have formula in them?

thanks again

 

[Guest]

Dave! You're awesome! Oh, yes, my syntax did not have the space in between
the quotes. By the way, the reason I need this as automated as possible is
because I'm sending the report to non-Excel familiar people. So I'm trying
to make it as fool-proof as possible and without them having to do anything
extra as possible. So anything that will need them to do anything everytime,
is out-- so options 1 to 3 are probably out. But option 4 is awesome! Thank
you very much!!!

 

[Dave Peterson]

It's a very nice technique.

Dave! You're awesome! Oh, yes, my syntax did not have the space in between
the quotes. By the way, the reason I need this as automated as possible is
because I'm sending the report to non-Excel familiar people. So I'm trying
to make it as fool-proof as possible and without them having to do anything
extra as possible. So anything that will need them to do anything everytime,
is out-- so options 1 to 3 are probably out. But option 4 is awesome! Thank
you very much!!!