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]

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.

GWteB, First, I appreciate you still trying to solve my problem. Second, when I print pdf I still get three pages. This is how I'm doing it without running a macro.

I select the field (A1-J133), Run the macro to hide the rows, make sure my page breaks are in the correct places, and then print by selection. It prints one page double sided and one blank page.+
Here is a link that you can look at what it is giving me.

Printtest1.pdf

Shared with Dropbox

www.dropbox.com

 

[Busscheduler]

GWteB, First, I appreciate you still trying to solve my problem. Second, when I print pdf I still get three pages. This is how I'm doing it without running a macro.

I select the field (A1-J133), Run the macro to hide the rows, make sure my page breaks are in the correct places, and then print by selection. It prints one page double sided and one blank page.+
Here is a link that you can look at what it is giving me.

Printtest1.pdf

Shared with Dropbox

www.dropbox.com

In speaking to one of my other coworkers, he might have figured out why the blank page is printing. When we start out, the section we want to print is 3-4 pages. After hiding all of the rows we don't need, we move the page breaks to make the section pages 1 and 2. All of the hidden rows in the middle are printing as a blank page. Hope this explanation helps. Thanks again for all of the work.

 

[GWteB]

All of the hidden rows in the middle are printing as a blank page.

Strictly the cause is slightly different. There are manually applied page breaks either at the beginning or within an area of contiguous hidden rows.
When printing a certain worksheet range, hidden rows are ignored (thus not printed as intended) and the automatic page breaks will be shifted. Manually applied page breaks within that particular range however aren't ignored nor shifted, causing you an unintended (blank) page.

The most obvious solution would be to permanently remove the manually enforced page breaks that are either at the beginning or within an area of potentially hidden rows. Since you use formulas to determine whether or not to hide a row, I don't know if this provides a workable solution for you.

I've updated your post #17 code in a way so it accepts a worksheet range as an argument. The macro now does both the hiding and the printing and may be called as shown in the two examples.
If the code turns out not to be immediately applicable, then you will at least have an idea of how to proceed.

Sub Print_Monday()
   
    Dim r As Range
    Set r = ActiveSheet.Range("A1:J133")
    ' trigger CF on Bus 1 (first item)
    Call HideRowsAndPrint(r, 1)
End Sub

Sub Print_Tuesday()
   
    Dim r As Range
    Set r = ActiveSheet.Range("A134:J266")
    ' trigger CF on Bus 5 (third item)
    Call HideRowsAndPrint(r, 3)
End Sub

Sub HideRowsAndPrint(ByVal argRange As Range, ByVal argBusItem As Long)

    Dim a()   As Variant
    Dim rng   As Range
    Dim i     As Long

    If argBusItem <= 0 Then Exit Sub
   
    ' Main
    With argRange

        ' >> trigger CF on desired bus
        i = argBusItem - 1

        ' copy cell in column L and paste in column K first row
        ' col 8 equals L:L because A:D & E:H are merged
        Set rng = .Cells(1, 1).Offset(i, 8)
        rng.Copy Destination:=rng.Offset(-i, -1)

        ' Disable screen updating (CF triggering) and events triggering
        Application.ScreenUpdating = False
        Application.EnableEvents = False
       
        .EntireRow.Hidden = False
        a() = .Offset(, 10).Resize(, 1).Value

        ' Collect rows to be hidden in the rng
        Set rng = Nothing
        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

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

        .PrintOut Copies:=1
        .EntireRow.Hidden = False

    End With

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

End Sub

 

[Busscheduler]

Strictly the cause is slightly different. There are manually applied page breaks either at the beginning or within an area of contiguous hidden rows.
When printing a certain worksheet range, hidden rows are ignored (thus not printed as intended) and the automatic page breaks will be shifted. Manually applied page breaks within that particular range however aren't ignored nor shifted, causing you an unintended (blank) page.

The most obvious solution would be to permanently remove the manually enforced page breaks that are either at the beginning or within an area of potentially hidden rows. Since you use formulas to determine whether or not to hide a row, I don't know if this provides a workable solution for you.

I've updated your post #17 code in a way so it accepts a worksheet range as an argument. The macro now does both the hiding and the printing and may be called as shown in the two examples.
If the code turns out not to be immediately applicable, then you will at least have an idea of how to proceed.

Sub Print_Monday()
  
    Dim r As Range
    Set r = ActiveSheet.Range("A1:J133")
    ' trigger CF on Bus 1 (first item)
    Call HideRowsAndPrint(r, 1)
End Sub

Sub Print_Tuesday()
  
    Dim r As Range
    Set r = ActiveSheet.Range("A134:J266")
    ' trigger CF on Bus 5 (third item)
    Call HideRowsAndPrint(r, 3)
End Sub

Sub HideRowsAndPrint(ByVal argRange As Range, ByVal argBusItem As Long)

    Dim a()   As Variant
    Dim rng   As Range
    Dim i     As Long

    If argBusItem <= 0 Then Exit Sub
  
    ' Main
    With argRange

        ' >> trigger CF on desired bus
        i = argBusItem - 1

        ' copy cell in column L and paste in column K first row
        ' col 8 equals L:L because A:D & E:H are merged
        Set rng = .Cells(1, 1).Offset(i, 8)
        rng.Copy Destination:=rng.Offset(-i, -1)

        ' Disable screen updating (CF triggering) and events triggering
        Application.ScreenUpdating = False
        Application.EnableEvents = False
      
        .EntireRow.Hidden = False
        a() = .Offset(, 10).Resize(, 1).Value

        ' Collect rows to be hidden in the rng
        Set rng = Nothing
        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

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

        .PrintOut Copies:=1
        .EntireRow.Hidden = False

    End With

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

End Sub

I know this is going to be ugly, but I made some changes using my limited knowledge of VBA coding. There is probably a shorter way, code wise, to make all of this happen but I don't know it.
I discovered that if I set the page breaks at the end of the first page of each day, which stays constant, and at the end of the day, which is always constant, and then run the macro I get pretty much what I want without the hidden page. When I do this the pages pretty much stay the same as far as printing, so I set the print option to page 1 and page 2 instead of "selection".
Here is the monster that I made:

(There is probably a way to make the print loop through r,1 to r,4 but I don't know how to do that)
(I commented out all of the changes that I made after running the code. )

Sub Print_Monday()
   
    Dim r As Range
    Set r = ActiveSheet.Range("A1:J133")
    ' trigger CF on Bus 1 (first item)
    Call HideRowsAndPrint(r, 1) ' Print Route 1
    '       Call HideRowsAndPrint(r, 2) ' Print Route 3
    '       Call HideRowsAndPrint(r, 3) ' Print Route 5
    '       Call HideRowsAndPrint(r, 4) ' Print Route 7
    
   '        Select cell K1 and change value to "??" so that no route is active CF
   '        Range("K1").Select
   '        Selection.Value = "??"
   
   '        Macro was not hiding rows after printing so I called HideRows1() at the end to hide all rows
   '        Call HideRows1()
    
    
End Sub

Sub HideRowsAndPrint(ByVal argRange As Range, ByVal argBusItem As Long)

    Dim a()   As Variant
    Dim rng   As Range
    Dim i     As Long

    If argBusItem <= 0 Then Exit Sub
   
    ' Main
    With argRange

        ' >> trigger CF on desired bus
        i = argBusItem - 1

        ' copy cell in column L and paste in column K first row
        ' col 8 equals L:L because A:D & E:H are merged
        Set rng = .Cells(1, 1).Offset(i, 8)
        rng.Copy Destination:=rng.Offset(-i, -1)

        ' Disable screen updating (CF triggering) and events triggering
        Application.ScreenUpdating = False
        Application.EnableEvents = False
       
        .EntireRow.Hidden = False
        a() = .Offset(, 10).Resize(, 1).Value

        ' Collect rows to be hidden in the rng
        Set rng = Nothing
        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

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

        .PrintOut Copies:=1
       ' .PrintOut From:=1, To:=2, Copies:=1
        .EntireRow.Hidden = False
      

    End With

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

End Sub

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

Again, thanks for all of the hard work. I have enjoyed trying to figure this one out.

 

[GWteB]

You are welcome and thanks for letting me know.
Good luck with your project!