MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Removing blank rows when printing

Posted by Brian on December 02, 2000 9:51 AM

Is there a way to print only rows that contain data while printing an excel worksheet. The empty rows I don't want to print do have cells with formulas but the formula is triggered only if data is entered in a cell in another worksheet. My report always has rows with blank cells and the report is therefore full of blank lines when printed. Thanks.

Posted by Celia on December 02, 2000 5:53 PM

Here's one way :-

1.Let's say your data covers columns A:E and the last row is row 10.
2.In any blank column(let's say col F), select F1:F10 and type the formula :-
Enter by pressing Ctrl+Enter
3.With F1:F10 still selected, go to Edit>GoTo>Special>Formulas and check the Text box only. Click OK.
4.Go to Format>Row>Hide and click OK.
5.Hide column F.
7.Select column F and unhide the hidden rows.
8.Delete column F.

You can record a macro for the above with the macro recorder.
In the macro the first line will read :- Range("F1:F10").Select. Delete this line - before running the macro, you will first need to select the appropriate cells in column F.
In order to avoid having to select ANY cells before running the macro, add the following line at the start of the macro :-
Intersect(ActiveSheet.UsedRange, Range("E:E")).Offset(0, 1).Select
This selects the appropriate cells in column F.

If you wish, you can also "tidy up" the macro so that it reads as follows :-

Sub Print_NonBlank_Rows()
Application.ScreenUpdating = False
With Intersect(ActiveSheet.UsedRange, Range("E:E")).Offset(0, 1)
.FormulaR1C1 = "=IF(SUM(RC[-5]:RC[-1])=0,"""",SUM(RC[-5]:RC[-1]))"
.SpecialCells(xlCellTypeFormulas, 2).EntireRow.Hidden = True
End With
Columns("F:F").EntireColumn.Hidden = True
ActiveSheet.PrintOut Copies:=1
With Columns("F:F")
.EntireRow.Hidden = False
.Delete Shift:=xlToLeft
End With


Posted by Celia on December 03, 2000 3:44 PM

Re: One more point

The method per above will only work if rows that are not blank have at least one cell per row containing a number.
The following should work for all situations (but probably takes longer to run) :-

Sub Print_NonBlank_Rows()
Dim rng As Range, cell As Range, toHide As Range
Dim r&, c%, b%, n%
Set rng = ActiveSheet.UsedRange
For r = 1 To rng.Rows.Count
b = 0
For c = 1 To rng.Columns.Count
If rng.Cells(r, c).Value = 0 Then
b = b + 1
End If
Next c
If b = rng.Columns.Count Then
If n = 0 Then
Set toHide = rng.Cells(r, c).EntireRow
n = 1
Set toHide = Union(toHide, rng.Cells(r, c).EntireRow)
End If
End If
Next r
toHide.EntireRow.Hidden = True
ActiveSheet.PrintOut Copies:=1
toHide.EntireRow.Hidden = False
End Sub


Posted by Celia on December 03, 2000 3:49 PM


The line that reads :
If rng.Cells(r, c).Value = 0 Then

Should read :
If rng.Cells(r, c).Value = "" Then