SpecialCells(xlCellTypeBlanks)

B

Bob

When printing I am using the following code to hide empty rows if the cell is
empty.
If there is a formula in a cell with no value it does not hide the row.
What should the code be to do this?

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet1" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
On Error Resume Next
.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden =
True
.PrintOut
.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden =
False
On Error GoTo 0
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

Thank you for your help.
Bob
 
D

Dave Peterson

A cell that contains a formula (even if it evaluates to "") isn't empty and
..specialcells(xlcelltypeblanks) will apply to empty cells.

Maybe you could use data|Filter|autofilter to hide those rows--or loop through
each of the cells in columns A to hide the rows, then print, and then undo it.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top