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" wrote:

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

Storm wrote:

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.

--

Dave Peterson