Don't Print Hidden Rows

[DanielleWebb]

I have a worksheet with hidden rows and Page Breaks. I know that the page breaks are causing my worksheet to print blank pages where I have page breaks and hidden rows.

I need the page breaks or the form just prints a mess... I tried eliminating them - but that didn't work at all.

I need a macro, which will examine the worksheet, look for the hidden rows, exclude them from the print area, and then Print the worksheet. Can it be done?

Thanks In advance.

(also, on a side note, if you show me the code can you tell me why/how it works? I enjoy the learning!)

 

[DanielleWebb]

When you click Debug, which line is highlighted?

This one: pb = Array(100, 181, 262, 343, 424, 505, 586, 667, 748)

 

[VoG]

That is strange. I did find another error corrected in red below. This code does run without error but I'm not sure if it will do what you want.

Sub PrtVis()
Dim pb, i As Integer
pb = Array(100, 181, 262, 343, 424, 505, 586, 667, 748)
With ActiveSheet
    .PageSetup.PrintArea = .UsedRange.SpecialCells(xlCellTypeVisible).Address
    .PageSetup.Orientation = xlPortrait
    .PageSetup.Zoom = False
    .PageSetup.FitToPagesTall = 1
    For i = LBound(pb) To UBound(pb)
        .HPageBreaks.Add Before:=.Range("A" & pb(i))
    Next i
    .PrintPreview
End With
End Sub

Change .PrintPreview to .PrintOut after testing.

 

[VoG]

Actually, for the page breaks to work:

Sub PrtVis()
Dim pb, i As Integer
pb = Array(100, 181, 262, 343, 424, 505, 586, 667, 748)
With ActiveSheet
    .PageSetup.PrintArea = .UsedRange.SpecialCells(xlCellTypeVisible).Address
    .PageSetup.Orientation = xlPortrait
    For i = LBound(pb) To UBound(pb)
        .HPageBreaks.Add Before:=.Range("A" & pb(i))
    Next i
    .PrintPreview
End With
End Sub

 

[seenfresh]

VoG

Would you need to reset all page breaks before adding new one's? Just a thought.

Sub PrtVis()
Dim pb, i As Integer
pb = Array(100, 181, 262, 343, 424, 505, 586, 667, 748)
With ActiveSheet
    .ResetAllPageBreaks ' remove all page breaks
    .PageSetup.PrintArea = .UsedRange.SpecialCells(xlCellTypeVisible).Address
    .PageSetup.Orientation = xlPortrait
    For i = LBound(pb) To UBound(pb)
        .HPageBreaks.Add Before:=.Range("A" & pb(i))
    Next i
    .PrintPreview
End With
End Sub

 

[VoG]

VoG

Would you need to reset all page breaks before adding new one's? Just a thought.

Sub PrtVis()
Dim pb, i As Integer
pb = Array(100, 181, 262, 343, 424, 505, 586, 667, 748)
With ActiveSheet
    .ResetAllPageBreaks ' remove all page breaks
    .PageSetup.PrintArea = .UsedRange.SpecialCells(xlCellTypeVisible).Address
    .PageSetup.Orientation = xlPortrait
    For i = LBound(pb) To UBound(pb)
        .HPageBreaks.Add Before:=.Range("A" & pb(i))
    Next i
    .PrintPreview
End With
End Sub

Yes, that's a good point. Thanks.

 

[seenfresh]

Also a suggestion is to allow the user to input the amount of copies he/she would like to print...

Sub PrtVis()
Dim pb, i As Integer
'Dim CopiesCount as Long
'CopiesCount = Application.InputBox("How many copies do you want", Type:=1)

pb = Array(100, 181, 262, 343, 424, 505, 586, 667, 748)
With ActiveSheet
    .ResetAllPageBreaks ' remove all page breaks
    .PageSetup.PrintArea = .UsedRange.SpecialCells(xlCellTypeVisible).Address
    .PageSetup.Orientation = xlPortrait
    For i = LBound(pb) To UBound(pb)
        .HPageBreaks.Add Before:=.Range("A" & pb(i))
    Next i
'For CopieNumber = 1 To CopiesCount
    .PrintPreview 'Change .PrintPreview to .PrintOut when tested
'Next CopieNumber
End With
End Sub

Of course the .PrintPreview would need to be changed to .PrintOut and activate the CopieNumber arguments and this should work well...

 

[seenfresh]

Hi VoG

FYI

I had a look at Danielle's file and have adjusted the code as follows in order for it to meet her desired formatting... I had to mess around a little with the page breaks and zoom to obtain the correct formatting under the different circumstances of the user.

Sub PrtVis()

Sheet1.Unprotect Password:=""
Dim pb, i As Integer
Dim CopiesCount As Long
CopiesCount = Application.InputBox("How many copies do you want", Type:=1)

pb = Array(100, 181, 262, 343, 424, 505, 586, 667, 748)


With ActiveSheet

    .ResetAllPageBreaks ' remove all page breaks from
    .PageSetup.PrintArea = "$D$1:$W$844"
    .PageSetup.FitToPagesWide = 1
    .PageSetup.Zoom = 75
    .PageSetup.Orientation = xlPortrait
    For i = LBound(pb) To UBound(pb)
   
        If .Rows(pb(i)).Hidden = True Then
        .HPageBreaks.Add Before:=.Cells(749, 1)
        Else
        .HPageBreaks.Add Before:=.Cells(pb(i), 1)
        .HPageBreaks.Add Before:=.Cells(749, 1)
       
        End If
    Next i
For CopieNumber = 1 To CopiesCount
    .PrintPreview 'Change .PrintPreview to .PrintOut when tested
Next CopieNumber
End With
Sheet1.Protect Password:=""
End Sub

If no other object changes are made to the file the code should do the trick.

 

[DanielleWebb]

It's gorgeous - works beautifully.

Thank you so much for your help )