Hide Rows with Formulas

[Guest]

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.
Can the code below be changed to accomplish 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

 

[Guest]

replace

..PrintOut

with

call hide_um
..PrintOut
call show_um

Where:

Sub hide_um()
Dim r As Range, rr As Range
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
Set r = Intersect(r, Columns("A"))
For Each rr In r
If rr.Value = "" Then
rr.EntireRow.Hidden = True
End If
Next
End Sub
Sub show_um()
Dim r As Range, rr As Range
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
Set r = Intersect(r, Columns("A"))
For Each rr In r
If rr.Value = "" Then
rr.EntireRow.Hidden = False
End If
Next
End Sub

 

[Guest]

Is this correct? It is not working.

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
Call hide_um
.PrintOut
Call show_um
.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden =
False
On Error GoTo 0
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub
Sub hide_um()
Dim r As Range, rr As Range
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
Set r = Intersect(r, Columns("A"))
For Each rr In r
If rr.Value = "" Then
rr.EntireRow.Hidden = True
End If
Next
End Sub
Sub show_um()
Dim r As Range, rr As Range
Set r = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas)
Set r = Intersect(r, Columns("A"))
For Each rr In r
If rr.Value = "" Then
rr.EntireRow.Hidden = False
End If
Next
End Sub

Bob