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!)

 

[seenfresh]

Greetings,

Try resetting your page breaks and print area by right clicking anywhere in print preview mode, then set your print area using the following steps:

http://spreadsheets.about.com/od/excel101/ss/excel_printing_6.htm

 

[DanielleWebb]

Greetings,

Try resetting your page breaks and print area by right clicking anywhere in print preview mode, then set your print area using the following steps:

http://spreadsheets.about.com/od/excel101/ss/excel_printing_6.htm

Hi Seenfresh -

Thanks for your input, it's not really what I am looking for though.

Here is the scenario:

I have a worksheet which is being used as a form. People who are less savvy with excel will be filling in various fields to answer questions.

Each person who uses the form will be different, and their scenarios will be different.

One "section" of the form is a section for information about family members. Each Family Member section is hidden. I have built a section for up to 8 family members. When the user needs to add information about a family member, he clicks a button "Add Family Member". A macro runs and unhides fields related to the family member. The user then tells me if the family member is employed. He hits the Yes button and fields regarding the family member's employment unhide. If the family member is a student, the user hits Yes and a section about the school opens.

Each Family Member section is a "page" set with a page break. There are sections before and After the family member sections. I want a macro to attach to a button that will print:

Section 1
Any Family Member Sections which have been unhidden but ignore the sections which remain hidden (and therefore do not contain any data)
Section 3

Can it be done? So far, I have not found any solution

 

[seenfresh]

I understand your situation better from your explanation...

Can it be done?

Just about anything can be done if it is logical, but the complexity of the request can take some time.

For each of your Family Member sections do you require them to be printed on separate pages? I am assuming you have a top section on your form followed by your family member sections and a footer section?

Off the top of my head using VBA you can set your print area to include everything and then assign page breaks before and after your family member sections depending on how many sections are unhidden at the time... If this is ultimately what is desired and looks appropriate.

Do you have any VBA experience? I will need some additional information like your worksheet print area and what rows each of your family member sections begin and end as well as which row your headers if any are in that you would like to repeat...etc

 

[DanielleWebb]

I really appreciate your help. )

I sent you a PM...

 

[VoG]

You can try this but it insists on printing each section on a new page - not sure why

Sub PrtVis()
With ActiveSheet
    .PageSetup.PrintArea = .UsedRange.SpecialCells(xlCellTypeVisible).Address
    .PageSetup.Orientation = xlPortrait
    .PageSetup.Zoom = False
    .PageSetup.FitToPagesTall = 1
    .PrintOut
End With
End Sub

 

[DanielleWebb]

VoG - Really close!

It did print only the visible cells, unfortunately, it first removed the page breaks which are needed.

I need page breaks above the following rows - this keeps each family member on his/her own page (just makes the data easier to access and utilize)

100
181
262
343
424
505
586
667
748

 

[VoG]

Untested

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" & i)
    Next i
    .PrintOut
End With
End Sub

 

[DanielleWebb]

I'm getting an Application-defined or object-defined error.. Runtime 1004

Hmmmmmmmm ~thoughtful~

 

[VoG]

When you click Debug, which line is highlighted?