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

Brian
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 :-
=IF(SUM(A1:E1)=0,"",SUM(A1:E1))
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.
6.Print.
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

Celia

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
Else
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

Celia

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

Correction


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

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

Celia