Hide Empty Rows When Printing

[Bob]

I am trying to use this code to hide all empty rows in column A when printing
I would like to start in row 6 an end in row 2000 so my header will print. It
doesn't work can you tell why?

Sub Hide_Print_Unhide()
Dim rw As Long
Application.ScreenUpdating = False

With Sheets("Production")
For rw = 1 To 2000
If Application.WorksheetFunction.CountA( _
.Cells(rw, 1).Range("A1:A2000")) = 0 Then _
.Rows(rw).Hidden = True
Next rw
.PrintPreview ' for testing use .PrintPreview
.Range("A1:A2000").EntireRow.Hidden = False
End With

Application.ScreenUpdating = True
End Sub

Thank you for your help,
Bob

 

[Ron de Bruin]

Try this Bob if they are empty

http://www.rondebruin.nl/print.htm#Hide

See this part

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

Change .Columns("A") to .Range("A6:A2000")

 

[Ron de Bruin]

Hi Bob

I think your cells in A are not Blank

You can test it like this

=ISBLANK(A1)

Do you have formulas in the A column ?

 

[Bob]

Yes I have a links in every cell in the in the A column to another sheet some
have information showing some don't that is why I am trying to hide them in a
printed report. Is there a way around this to still hide them?

Bob

 

[Ron de Bruin]

Hi Bob

Try this macro then to Hide/Print/Unhide
Delete "preview:=True" if it is working correct


Sub Hide_Print_Unhide()
Dim cell As Range
Application.ScreenUpdating = False
With Sheets("Production")
For Each cell In .Range("A6:A2000")
If cell.value = "" Then cell.EntireRow.Hidden = True
Next cell
.PrintOut preview:=True
.Range("A6:A2000").EntireRow.Hidden = False
End With
Application.ScreenUpdating = True
End Sub