Macro is printing hidden sheets that I don't need

[Busscheduler]

I know that this has been covered in other threads, and I have searched some of those but have not found a code that will work.

I have a sheet that contains all seven days of the week, and depending on which times the theme parks close, each day uses different pages that I want to print. For example, Monday is made up of pages 1-4 and some days I would need to print 1 and 2, and other days 1 and 3 or 1 and 4, etc. The rows that I don't need printed are hidden before printing. I recorded a macro using "Print selection" and included the full range of the 4 pages for Monday:

Sub PrintMondaySelection()
'
' PrintMonday Macro
'

'
Range("L1").Select ' Copies which route should be highlighted
Selection.Copy
Range("K1").Select ' Pastes the route in cell K1. This activates the conditional formatting.
ActiveSheet.Paste
Range("A1:J133").Select ' Range of cells for pages 1-4
Application.CutCopyMode = False
Selection.PrintOut Copies:=1, Collate:=True
Range("A1:D1").Select

End Sub

Using this macro, I get the pages I want printed but it also includes a blank page for each hidden page. I'm not sure how to get it to stop that.

Thanks.

 

[Busscheduler]

Do you really have multiple sheets (tabs) in your Excel file?
Or is it really a matter of one single sheet with many rows that are printing on to multiple sheets?

This file has 26 different tabs.

Is it printing hidden rows?
Or do you have blank rows at the bottom that are being printed (i.e. you do not have data all the way down to row 133)?

The hidden rows are not being printed, only the blank page as page 3. There is data in every row down to row 133 in the selection area. The sheet has 931 rows total with data in every row, some of which are formulas and some are just text and numbers.

Also note that if printing from columns A to J exceeds the width of a single page, you will get this to cause to print twice as many sheets as you want.
If you change Page Setup to "Fit to 1 page wide by 9999 tall", that should prevent that from happening.

The print area doesn't exceed the width of one page so we don't have this issue.

 

[GWteB]

Your data ends at row 111 in your example workbook but your print area is larger.
Not sure if this works.

Sub Print_Example_r2()
    Dim rng As Range
    With ActiveSheet
        .Range("L1").Copy Destination:=.Range("K1")
        .ResetAllPageBreaks
        .PageSetup.PrintArea = ""
        Set rng = .Range("A1:J" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        .PageSetup.PrintArea = rng.Address
        On Error Resume Next
        .VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
        On Error GoTo 0
        rng.PrintOut Copies:=1, Collate:=True
        .Range("A1:D1").Select
    End With
End Sub

 

[GWteB]

Missed your second upload. That worksheet is considerably larger and most of the horizontal page breaks are already manually placed in the desired locations. My code will remove these horizontal page breaks and that is probably not your intention. BTW, I am not able to reproduce your blank page issue ...

 

[Busscheduler]

Missed your second upload. That worksheet is considerably larger and most of the horizontal page breaks are already manually placed in the desired locations. My code will remove these horizontal page breaks and that is probably not your intention. BTW, I am not able to reproduce your blank page issue ...

The last code printed 8 pages. Unfortunately, I'm going to have to table this for another time since I am finishing a project by end of business today. Thanks for all of your time and maybe we can pick this up another day. If you get any ideas in the meantime maybe you can message me? Not sure how that works on this forum.

Thanks again for your efforts.

Regards.

 

[GWteB]

You are welcome.
It would come in handy if you could post the code you used for selective printing so far. Perhaps that code provides more clarity about the cause of the blank pages.
I would also like to know whether the data on that worksheet is still growing or whether it stays as is, apart from any hidden rows.
I am willing to help you to solve the issue you're having so I'll keep watching this thread.

 

[Busscheduler]

This is the code that I tried to print selection with. It is just a recorded macro (print selection, 2 sided)

Sub PrintMondaySelection()
'
' PrintMonday Macro
'

'
Range("L1").Select ' Copies which route should be highlighted
Selection.Copy
Range("K1").Select ' Pastes the route in cell K1. This activates the conditional formatting.
ActiveSheet.Paste
Range("A1:J133").Select ' Range of cells for pages 1-4
Application.CutCopyMode = False
Selection.PrintOut Copies:=1, Collate:=True
Range("A1:D1").Select

End Sub

The data on the sheet stays the same but the rows to hide change with the different closing times for the theme parks. In the example that I sent you, the rows are hidden based on a 9:00 pm closing time. If the time changes to 8:00 pm or 10:00 pm then the rows that are hidden would also change to accommodate the different schedules.

In the past when we worked with different sheets we would just print a range of pages, say page 1 and 2, and that would encompass all of the information. On this sheet, the rows change, so we would want to print selection instead of page numbers, hoping that it would only print visible rows and not hidden rows.

 

[Busscheduler]

One other thought: I am using this macro to hide the rows, and since we haven't had this problem before, could this be causing it? I just started using this recently.

Sub HideRows1()

  Dim a()
  Dim rng As Range
  Dim i As Long
  Dim ws As Worksheet

  ' Disable screen updating (CF triggering) and events triggering
  Application.ScreenUpdating = False
  Application.EnableEvents = False

  ' Main
  Set ws = ActiveSheet
  With ws
    .UsedRange.EntireRow.Hidden = False
    a() = .Range("K1", .Cells(.Rows.Count, "K").End(xlUp)).Value
    ' Collect rows to be hidden in the rng
    For i = 1 To UBound(a)
      If a(i, 1) = "HIDE" Then
        If rng Is Nothing Then
          Set rng = .Rows(i)
        Else
          Set rng = Union(rng, .Rows(i))
        End If
      End If
    Next
  End With

  ' Hide rows
  If Not rng Is Nothing Then
    rng.EntireRow.Hidden = True
  End If

  ' Restore screen updating and events triggering
  Application.EnableEvents = True
  Application.ScreenUpdating = True
 

End Sub

 

[GWteB]

One other thought: I am using this macro to hide the rows, and since we haven't had this problem before, could this be causing it? I just started using this recently.

The way you hide a row has nothing to do with your issue (nice code BTW). For this moment I'm going offline (CET) but I'll keep trying to reproduce your issue.

 

[Busscheduler]

The way you hide a row has nothing to do with your issue (nice code BTW). For this moment I'm going offline (CET) but I'll keep trying to reproduce your issue.

Yes, that code works very well in hiding the rows on my sheet. I can't take the credit though as it came from ZVI, another poster on this site. I was very thankful for that.

 

[GWteB]

Hi @Busscheduler, I have done several tests with the second workbook you uploaded. Prior to my testing, I set Microsoft Print to PDF as the default printer. I am not getting any blank pages. Maybe you have already tested in this way yourself, if not I would advise you to do so. If your problem does not arise with the Microsoft Print to PDF, the printer itself must be the cause of your issue rather then Excel or your workbook.