May 24, 2018
I have an excel sheet with 7 different 'forms' on it ranging from A1:O43, A44:O77, A78:O111, A112:O145, A146:O167, A168:O189, A190:O220. Each form is on 1 page. If a form is not used, I have made buttons to hide the form on the workpage (code below) but it would still print a blank page (I know you can manually change print areas/print selection). The last form (A190:O220) is a totals page which should only be added to the print area if 2 or more forms are used. Im looking for a solution to include/remove these forms from the print area when their respective buttons are clicked to show/hide them from the worksheet (if possible). If this is not possible, an alternative would be to have a new macro to set the print area to current active cells (not including the hidden rows of forms not in use). Im not advanced at coding so I have essentially been going through each possible combination of forms (code below) that have a value on them to include in the print area. Im sure there is a much faster method out there.. any help would be appreciated. Thanks!
Button to hide/show 1 of the forms (for example):
Sub ToggleButton1 ()
.EntireRow.Hidden = Not .EntireRow.Hidden
Set Print Area - Some of the Combinations (checks to see if a cell in a form has any values and sets it as the print area):
If Range("G6").Value <> "" And Range("G117").Value <> "" Then
ActiveSheet.PageSetup.PrintArea = "$A$1:$O$43, $A$112:$O$145, $A$190:$O$220"
If Range("G6").Value <> "" And Range("G117").Value <> "" And Range("G83").Value <> "" And Range("G49") <> "" Then
ActiveSheet.PageSetup.PrintArea = "$A$1:$O$43, $A$44:$O$77, $A$78:$O$111, $A$112:$O$145, $A$190:$O$220"
June 25, 2016
Hi B B
Welcome to the forum.
It is not necessary to hide the rows.
Since you already have 7 fixed ranges, I have included 6 check boxes for you to selected your report(s) to print.
It will only print the Summary report if 2 or more reports are printed.
The macro will check each Checkbox to see if it is selected. It will then print the required range in its own page (you will need to adjust your print settings).
I have currently set the macro to PrintPreview for testing purpose. Change it to Printout when it is working correctly.
Hope this helps.