Excluding Rows During Printout

[kjell.uddeborg]

Hi,

Is there a way to automatically exclude rows when printing an Excel
sheet?

I have a list of items (in two colums) in a work sheet similar to
this:

Task Time
----------- -------
Task A: 1
Task B: 0
Task C: 2
Task D: 0
Task E: 0
Task F: 0
Task G: 4
Task H: 1

When I print the worksheet I only want to print lines where the time
cell is not zero. So the printout from the previous data would look
like this:

Task Time
----------- -------
Task A: 1
Task C: 2
Task G: 4
Task H: 1

Is there a way to do this?

Thanks for your help,
Kjell

 

[Gord Dibben]

Autofilter for Time <> 0

Print the results.


Gord Dibben MS Excel MVP

 

[kjell]

Autofilter for Time <> 0

Print the results.

Gord Dibben  MS Excel MVP

Thanks for your answer! Very cool! Do you know if it would be
possible to create a filter so that the "output", as described
earlier, would automatically end up on a different worksheet? That way
I would not need to turn the filter off and on as I'm changing the
data. When I'm done I would simply change to the other worksheet and
everything would already filtered.

Thanks for your help,
Kjell

 

[Gord Dibben]

You could just copy and paste the visible cells to another sheet.

Or use Advanced Filter to bring the results to another sheet.

See Debra Dalgleish's site for that.

http://www.contextures.on.ca/xladvfilter01.html#ExtractWs

Or you could use a macro to do it all for you.

See Ron de Bruin's site for code to filter a sheet and place results on
another sheet.

http://www.rondebruin.nl/copy5.htm#AutoFilter


Gord

 

[dranon]

Thanks for your answer! Very cool! Do you know if it would be
possible to create a filter so that the "output", as described
earlier, would automatically end up on a different worksheet? That way
I would not need to turn the filter off and on as I'm changing the
data. When I'm done I would simply change to the other worksheet and
everything would already filtered.

Easily done. Put in a new column A that increments every time your
time value is more than zero. It will then look like this:

Increment Task           Time
--------- -----------   -------
1 Task A:       1
1 Task B:       0
2 Task C:       2
2 Task D:       0
2 Task E:       0
2 Task F:       0
3 Task G:       4
4 Task H:       1

The formula in column a, for example in row 3, is:

=if(c3>0,a2+1,a2)

Then copy that down.

On you new sheet have a lookup function that shows the results based
on the row. Let's say you want to start the results on row 3 (rows 2
and 1 are headers). You would put this in cell A3:

=vlookup(row()-2,'MyOtherSheet'!A3:C10,2,0)

The result in A3 will be the first row that shows up with a positive
number.

Copy that down as far as you think you need. You can then get fancy
and encase the whole thing in an ISERR to replace the value with ""
for all rows where the value of row()-2 is greater than the highest
inicrement.

Good luck.