Dynamic Print Area Macro VBA

excely

New Member
Joined
May 23, 2018
Messages
9
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 ()
With Rows("78:111")
.Select
.EntireRow.Hidden = Not .EntireRow.Hidden
End With
End Sub


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"
End If
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"
End If
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top