Set Up A Dynamic Print Area In Your Excel Worksheet

Hello Excellers, welcome to another #macromondays blog post. I have a really nice little bit of VBA code for you today which allow you to set your Print area in your Excel worksheet to become dynamic. That’s right, set up this piece of VBA code in your Excel workbook and your print area with change as your active area in your worksheet changes. So, if you have a variable report or data set you to need to print, this little macro will automatically create a dynamic Print area for you with no hard work at all.

What Does The Macro Do?

This macro will automatically print the used or active range of cell in your worksheet

How Does The Macro Work?.

We use the PrintArea property to determine the range of cells to be included when printing the Excel worksheet. By combining it with the UsedRange property and storing the code in the worksheet’s Change Event it becomes dynamic as it triggered anytime there is a change in the Excel worksheet.

Step 1. Activate the Visual Basic Editor By hitting ALT+F11 shortcut of you can go to the Developer Tab | Visual Basic to open the Visual Basic Explorer Window. If you want to know how to enable the developer tab then you can check out my YouTube Video below for some simple Instructions.

Step 2. Double click on the Worksheet where you want to set your Print Area. In my example, it is Sheet 1. Select the Worksheet Object and the Change event from the Event drop-down list.

Step 3. We use the PageSetup.PrintArea Property which sets or returns the Print Area to be printed. We set this as the Active Sheet.

Step 3. This is where we set the Print Area as being the UsedRange. This returns a range object which is representing the rea of the worksheet that is being used. This is ideal as this can vary from time to time. The Used Range property represents the upper left and lowest right cells that are used in a worksheet. This Includes all cells that are in between. Sounds perfect for what we want, doesn’t it??

Step 4. Test Your Code! See in my example below where I change the Print Areas to demonstrate this Macro.

Do You Want To Copy The VBA Code And Try It Out For Yourself?.

[stextbox id=”grey”]

‘Macro ByHow To Excel At Excel

Private Sub Worksheet_Change(ByVal Target As Range)

ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.Address

End Sub

[/stextbox]

If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.