ExcelBanter - Hide Empty Rows When Printing

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Hide Empty Rows When Printing (https://www.excelbanter.com/excel-worksheet-functions/41388-hide-empty-rows-when-printing.html)

Hide Empty Rows When Printing

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

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).Entir eRow.Hidden = True
.PrintOut
.Columns("A").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden = False
On Error GoTo 0
End With

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Bob" wrote in message ...
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

This is what I am using now:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Production" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
On Error Resume Next

..Range("A6:A2000").SpecialCells(xlCellTypeBlanks) .EntireRow.Hidden = True
.PrintOut

..Range("A6:A2000").SpecialCells(xlCellTypeBlanks) .EntireRow.Hidden = False
On Error GoTo 0
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

It does not hide the rows, Why?

Thanks,
Bob

"Ron de Bruin" wrote:

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).Entir eRow.Hidden = True
.PrintOut
.Columns("A").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden = False
On Error GoTo 0
End With

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Bob" wrote in message ...
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

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 ?

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Bob" wrote in message ...
This is what I am using now:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Production" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
On Error Resume Next

.Range("A6:A2000").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = True
.PrintOut

.Range("A6:A2000").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = False
On Error GoTo 0
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

It does not hide the rows, Why?

Thanks,
Bob

"Ron de Bruin" wrote:

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).Entir eRow.Hidden = True
.PrintOut
.Columns("A").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden = False
On Error GoTo 0
End With

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Bob" wrote in message ...
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

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" wrote:

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 ?

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Bob" wrote in message ...
This is what I am using now:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Production" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
On Error Resume Next

.Range("A6:A2000").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = True
.PrintOut

.Range("A6:A2000").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = False
On Error GoTo 0
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

It does not hide the rows, Why?

Thanks,
Bob

"Ron de Bruin" wrote:

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).Entir eRow.Hidden = True
.PrintOut
.Columns("A").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden = False
On Error GoTo 0
End With

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Bob" wrote in message ...
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

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Bob" wrote in message ...
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" wrote:

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 ?

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Bob" wrote in message ...
This is what I am using now:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Production" Then
Cancel = True
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
On Error Resume Next

.Range("A6:A2000").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = True
.PrintOut

.Range("A6:A2000").SpecialCells(xlCellTypeBlanks). EntireRow.Hidden = False
On Error GoTo 0
End With
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
End Sub

It does not hide the rows, Why?

Thanks,
Bob

"Ron de Bruin" wrote:

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).Entir eRow.Hidden = True
.PrintOut
.Columns("A").SpecialCells(xlCellTypeBlanks).Entir eRow.Hidden = False
On Error GoTo 0
End With

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

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Bob" wrote in message ...
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