Hidden rows

[Borut]

I have 55 pages. Beacuse not are all full or not have data, I hide empty rows (all empty pages). In office (excel) 2010 works fine. But in 2013, print me blank page (pages 1, 4, 5,10 are full). Print 10 pages (blank and full) but from 11 to 55 is ok (not print).
Whay print blank pages too, if are hidden rows, not only full pages?

[Paul_Hossler]

Unhide all the rows, then select all the 'empty' columns from the end of your data to the end of the worksheet, and then delete the empty columns

See if that makes any difference

[Borut]

I found the problem. Problem is in the page breaks. If I hidde rows and add page breaks, blank pages are for the hiddn rows.

I dont now the solution for this

[SamT]

Delete the PageBreaks, I assume there are no VPageBreaks
For Each Sht In Sheets
With Sht
For Each PB In .HPageBreaks
If not PB.Location.Visible then PB.Delete
Next
End With
Next

Also check Excel's PageSetup. I have no Printer installed so I can't go there.

[Borut]

This is problem from office 2013. Before it works ok with page breaks. On web is a lot of forum for "Hidden rows result in print out of blank pages", but no solution...

[p45cal]

but no solution...
Perhaps there is.
The following works here, but only tested on sheets which print on 1 sheet wide, that is, the pages are vertically stacked. No vertical page breaks considered.
The problem only seems to arise with manually inserted page breaks.
2 problems with deleting all the hidden ones:

a) They're there for a purpose so one day may be needed again (and if there are a lot it's a pain to put them back).
b) You probably don't want to delete all the hidden ones; all but the last hidden one in each hidden area of rows - I think.
So I set about programmatically:

Making a note of which to delete
Deleting them
Printing
Restoring them


So:

Sub blah()
Dim VisibleArea As Range, PA As Range, HPBsToDelete As New Collection, ThisAreaHPBs As Collection
Dim HiddenAreas As Range, HiddenArea As Range, AllHPBs, idx As Long, LowestRow, HPB, PB, i As Long
Dim DeletedHPBs As Range, are As Range

With ActiveSheet
Set PA = Range(.PageSetup.PrintArea)
Set VisibleArea = PA.SpecialCells(xlCellTypeVisible)
Set HiddenAreas = RangeComp(VisibleArea, PA)
Set AllHPBs = .HPageBreaks
idx = 0
If Not HiddenAreas Is Nothing Then
For Each HiddenArea In HiddenAreas.Areas
Set ThisAreaHPBs = New Collection
LowestRow = 0
For Each HPB In AllHPBs
If Not Intersect(HiddenArea, HPB.Location) Is Nothing Then
ThisAreaHPBs.Add HPB
If HPB.Location.Row > LowestRow Then LowestRow = HPB.Location.Row
End If
Next HPB
For Each HPB In ThisAreaHPBs
If HPB.Location.Row < LowestRow Then
idx = idx + 1
HPBsToDelete.Add HPB, CStr(idx)
End If
Next HPB
Next HiddenArea
'Here we have a list of HPBs to delete so make a note of where they were then delete them:
For Each PB In HPBsToDelete 'where they are
If DeletedHPBs Is Nothing Then Set DeletedHPBs = PB.Location Else Set DeletedHPBs = Union(DeletedHPBs, PB.Location)
Next PB
For i = HPBsToDelete.Count To 1 Step -1 'delete them
HPBsToDelete.Item(CStr(i)).Delete
Next i
'so printout/preview
.PrintOut preview:=True 'in the preview, you can cancel the printout or not.
'then restore pagebreaks:
If Not DeletedHPBs Is Nothing Then
For Each are In DeletedHPBs.Areas
.HPageBreaks.Add are
Next are
End If
Else
MsgBox "No hidden rows to deal with, print normally"
End If
End With
End Sub

Function RangeComp(rngA As Range, rngB As Range) As Range
' Returns the Relative Complement of rngA in rngB
' RangeComp = rngB - RngA
Dim cell As Range
If rngA Is Nothing Then
Set RangeComp = rngB
ElseIf rngB Is Nothing Then
' nothing to do; will return Nothing
Else
For Each cell In rngB
If Intersect(cell, rngA) Is Nothing Then
If RangeComp Is Nothing Then
Set RangeComp = cell
Else
Set RangeComp = Union(RangeComp, cell)
End If
End If
Next
End If
End Function
RangeComp is a function stolen from shg here: https://www.excelforum.com/excel-general/658050-invert-selection-of-cells.html#post1978808
Works on the active sheet only - I suppose it could be tweaked (a lot) to work on the whole workbook.