How can i auto hide empty rows in printing in excel?

A

Arvi Laanemets

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.
 
P

Peter T

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
 
G

Gord Dibben

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
 
Top