Can I print an excel sheet and skip empty rows

  • Thread starter Thread starter lee1958
  • Start date Start date
L

lee1958

I am trying to print a sheet that has 100 rows, but I only want thos
rows that have data to print. Is this possible
 
can you pick out a column that always has data if there's something in that row?

If yes, you could apply Data|Filter|autofilter

filter that columns for non-blanks

Print the worksheet

Data|filter|showall

to get it back
 
Yes, that works, now to further complicate. I am trying to set up
quick and easy estimating tool that has a list of about 100 potentia
items. I only want to print items that have a non-zero quantity, an
your solution will work fine, is there a way to auto-majically swa
from "show all" to "filtered" mode when it's time to print, and the
back once the print is complete? Sounds like a keystroke macro, but
have no experience with that in Excel
 
I like to use the arrow dropdowns to filter my data. (I think it's quicker than
writing/debugging/changing a macro for what I want. And if you share it with
others, it'll be time well spent to learn how to use autofilter.)

But on the other hand, I hate going into each filter to set it back to (all).

I even hate Data|filter|showall.

But I like this option:

Tools|Customize|Commands tab|Data Category
Drag the showall icon to your favorite toolbar.

(It makes it pretty painless and (I think) more useful in all your workbooks.)

===
If you really want, you could record a macro that filters the worksheet the way
you want. I'd run the macro on demand--not automatic. Someday, you'll want to
print all the options.

I'd drop a button from the forms toolbar at the top of the worksheet and freeze
panes so that that button is always visible.

I used the first column in the autofilter range for this example:

Option Explicit

Sub hideZeros()

With ActiveSheet

If .FilterMode Then
.ShowAllData
End If

With .AutoFilter.Range
.AutoFilter Field:=1, Criteria1:="<>0"
End With

.PrintOut preview:=True

.ShowAllData

End With

End Sub

And get rid of that preview:=true when you're ready to turn it on for real.

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