Printing Only Rows With Values In Them

[robzrob]

In Workbook2 I’ve got formulas in all cells from A1 to J100. The
formulas ‘pull in’ values from the corresponding cells in Workbook1.
Not all rows in Workbook1 always have values in them, ie the values
may stop at row 20, 30, 45, whatever (but there won’t be any gaps). I
need to print Workbook2 sometimes and I only want to print the rows
where there are values, not the whole 100 rows with formulas in them,
but if I click ‘Print’ I always get the whole 100 rows printed,
whether there are values in them or not. In the 100 rows I’ve got
conditional formatting thus: If cell isn’t blank, put a border around
it, in Page Set Up, I’ve got Rows to repeat at top: $2:$2 and I’ve got
a header – which is just some text, but even if I take the conditional
formatting off and take the Rows to Repeat and header out, I still get
the 100 rows printed.

 

[Dave Peterson]

Maybe you could apply data|filter|autofilter to hide the cells that look empty.

If you don't like that...

Saved from a previous post (so you'll have to adjust the sheet names and column
letters and even the columns to print (A:X or B:z????):

If those formulas appear at the end of the data and you don't want to use
autofilter...

If I can pick out a column indicates if that row is used or not, then I like
this technique:

(I used column A in my sample, but you can use any column you want.)

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

(Make that 1000 big enough to extend past the last possible row.)

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

That last 3 represents the last column to print (A:C in my example).

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

If you go into file|page setup, you may find that the print range is changed to
a specific range. And you'll have to reapply the Print_Area name.

 

[robzrob]

Maybe you could apply data|filter|autofilter to hide the cells that look empty.

If you don't like that...

Saved from a previous post (so you'll have to adjust the sheet names and column
letters and even the columns to print (A:X or B:z????):

If those formulas appear at the end of the data and you don't want to use
autofilter...

If I can pick out a column indicates if that row is used or not, then I like
this technique:

(I used column A in my sample, but you can use any column you want.)

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

(Make that 1000 big enough to extend past the last possible row.)

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

That last 3 represents the last column to print (A:C in my example).

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

If you go into file|page setup, you may find that the print range is changed to
a specific range.  And you'll have to reapply the Print_Area name.

Thanks for the reply in other group. Sorry I wasn't clear about the
above, I haven't tried it because I don't understand it, any of it, at
all.