Macro 44: Dynamically Set the Print Area of a Worksheet
In certain situations, you may find yourself constantly adding data to your spreadsheets. When you
do, you may have to constantly resize the print area of the worksheet to encapsulate any new data
that you’ve added. Why keep doing this manually when you can implement a macro to dynamically
adjust the print area to capture any new data you’ve added?
How it works
In this simple macro, we use the PrintArea property to define the range of cells that will be
included when printing. As you can see, we are simply feeding the PrintArea property with the
address of the UsedRange property. The UsedRange property gives us a range that encompasses
the cells that have been used to enter data.
To keep this dynamic, we implement the code in the worksheet’s Change event:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.PageSetup.PrintArea =
ActiveSheet.UsedRange.Address End Sub
How to use it
To implement this macro, you need to copy and paste it into the Worksheet_Change event code
window. Placing the macro here allows it to run each time you double-click on the sheet.
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. In the Project window, find your project/workbook name and click the plus sign next to it in
order to see all the sheets.
3. Click on the sheet in which you want to trigger the code.
4. Select the Change event from the Event drop-down list (see Figure 4-8).
5. Type or paste the code.
Figure 4-8: Type or paste your code in the Worksheet_Change event code window.
