Auto Filter Causing Blank Hidden Pages to Print out

[pgleague070104]

In Excel 2013 -- I have created a worksheet that uses the auto filter function to hide rows (which are full pages when printed) of line items I don’t want to include in the final printout.

In order to get the format to look good when printed, I have to use manual page breaks throughout the worksheet. The sheet includes about 43000 rows which I have broken into 100+ pages (using page breaks)

Also note that I have specified the print area that includes only the columns I want to print. For example, the column that contains the auto filter, is NOT in the print range as I don’t want it showing up on the printout. Note that the columns in the print area do fit in the right/left print margins.

The problem is that when I apply the auto filter and hide rows, excel inserts blank pages for those pages that are hidden/filtered out. If I was printing to a printer I could just remove the blank pages and that would be fine. However, I am needing to print this to a PDF so I can’t have blank pages.

To clarify further, if I say have a 200 page document/worksheet prior to auto filter being applied. After auto filter is applied the document/worksheet is down to 120 pages on the screen which looks great. However, when I print it, excel spits out the 120 pages with text along with 80 blank pages in place of the pages are that are filtered out/hidden.

I cannot remove the hard page breaks as this is the formatting necessary should information be on the page for a certain month. Number of pages used changes every month.

I have searched the web for a macro that would print only visible pages on a sheet but have not been able to find one.


Any ideas would be much appreciated.

Thanks

 

[MARK858]

Copy the visible cells to another sheet then do a printout of that sheet? the page breaks could still be an issue.

 

[pgleague070104]

Thank you for you suggestion. However, If I copy visible cells to a new worksheet I then have to reformat the worksheet and add back all the manual pages breaks. This worksheet consists of 120 pages when formatted properly. I don’t want to have to reformat something that is already properly formatted. I simply don’t want the blank pages caused by hidden page breaks due to an auto filter to print.

I’m trying to locate a VB macro code to print the visible pages. The ones I have located print all visible cells in the worksheet. Not all cells in my worksheet are in the print area. I need one that uses the print area I have already set and prints the visible pages only.

 

[MARK858]

How do you decide where to put the manual page breaks?

 

[pgleague070104]

I insert a page break after the total revenue and net income for each event making every event two pages. Once the report is updated through the current month I filter for only the events I need to print. This causes the now hidden pages with manual page breaks to print as blank pages in my PDF.

 

[MARK858]

So do you have the words total revenue and net income in cells on the same line that you need to apply the page breaks to? (or consistent rows away from the page breaks)?

 

[ZVI]

Hi Pamella and welcome to MrExcel board!
Try using the below macros to print and save as pdf only visible cells

Sub PrintVisible()
  Dim rng As Range, s As String
  Set rng = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeVisible)
  s = ActiveSheet.PageSetup.PrintArea
  If Len(s) > 0 Then
    Set rng = Intersect(rng, Range(s))
  End If
  If rng Is Nothing Then
    MsgBox "Nothing to print", , "Exit"
    Exit Sub
  End If
  Application.EnableEvents = False
  rng.PrintOut
  Application.EnableEvents = True
End Sub
 
Sub SaveAsPdf()
  Dim FileName As String, Path As String, rng As Range, s As String
  Set rng = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeVisible)
  s = ActiveSheet.PageSetup.PrintArea
  If Len(s) > 0 Then
    Set rng = Intersect(rng, Range(s))
  End If
  If rng Is Nothing Then
    MsgBox "Nothing to print", , "Exit"
    Exit Sub
  End If
  With ThisWorkbook
    Path = .Path & "\"
    FileName = Left(.Name, InStrRev(.Name, ".")) & "pdf"
  End With
  FileName = Application.InputBox("Name of the PDF file", Path, FileName, Type:=2)
  If Trim(FileName) = "" Then
    MsgBox "Exit without saving"
    Exit Sub
  End If
  FileName = Path & FileName
  If Dir(FileName) <> "" Then
    If MsgBox("File exists. Do you want to overwrite it?", vbYesNo, FileName) = vbNo Then
      MsgBox "Exit without saving"
      Exit Sub
    End If
  End If
  Application.EnableEvents = False
  On Error GoTo exit_
  rng.ExportAsFixedFormat Type:=xlTypePDF, _
                            FileName:=Path & FileName, _
                            Quality:=xlQualityStandard, _
                            IncludeDocProperties:=True, _
                            IgnorePrintAreas:=False, _
                            OpenAfterPublish:=False
exit_:
  Application.EnableEvents = True
  If Err Then MsgBox Err.Description, vbExclamation, "Error - file is in usage"
End Sub

Regards,
Vlad

 

[ZVI]

Additionally you may put this code into ThisWorkbook module to fix a casual printing:

Option Explicit
 
Dim IsSaving As Boolean
 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
  Dim rng As Range, s As String
  Set rng = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeVisible)
  s = ActiveSheet.PageSetup.PrintArea
  If Len(s) > 0 Then
    Set rng = Intersect(rng, Range(s))
  End If
  If rng Is Nothing Then
    MsgBox "Nothing to print", , "Exit"
    Exit Sub
  End If
  Application.EnableEvents = False
  If IsSaving Then
    IsSaving = False
    'Application.OnTime Now, "SaveAsPdf"
  Else
    rng.PrintOut
    Cancel = True
  End If
  Application.EnableEvents = True
End Sub
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  IsSaving = True
End Sub
 
Private Sub Workbook_AfterSave(ByVal Success As Boolean)
  IsSaving = False
End Sub

Hope it will solve that printing issue.

 

[tomas_b]

Hello Vlad,
I was very happy, because your code worked.
Now I added grouping feature, as I would like to hide some information, which is not needed in my offer template. So when my rows are colapsed, it works like it should. But if I group rows, then it creates one blank page after each group (plus button). Could you please check it, if there is some possibility to let it work with both grouped and collapsed options?
Thank you,
Tomas