How can I auto hide empty rows in printing a table in excel?

Hi

In case all empty rows are at bottom of your table (I.e. you prepared some
table - entered formulas, formatted cells, etc. - and are now entering data,
then define a named range Print_Area for this sheet as a dynamic range, in a
way the range includes the rows with data only.

When you have empty rows between filled ones, then your luck is running out
IMHO.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )


"TM" wrote in message
...
How can I auto hide empty rows in printing a table in excel?

To do that totally automatically would require some VBA (in the BeforePrint
event in the ThisWorkbook module). Without code and not quite automatic try
this

Ensure there's at least one row above your table
Say your Table is in C2:J20
In a helper column either to left or right of your table, let's say in Col-B
B1: "nonEmpty" or whatever you want but not empty
B2: =COUNTIF(C2:J2,"")
- copy down to at least to the bottom of your table or beyond
- each cell should return the number of non empty cells
select B1
Data, Filter, AutoFilter

When you want to print, select the filter arrow in B1 and "0"
Blank rows should hide

When done reset the filter to All or clear it completely from the same Data
menu

Previously you should have set your print-area to exclude the helper column
and top filter row, probably just your table in C2:J20

Regards,
Peter T


"TM" wrote in message
...
How can I auto hide empty rows in printing a table in excel?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).EntireRow.Hidden = True
End If
Next r
End Sub


Gord Dibben MS Excel MVP

On Fri, 1 Aug 2008 02:25:00 -0700, TM wrote:

How can I auto hide empty rows in printing a table in excel?