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.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.
How To Excel At Excel – Macro Mondays Blog Posts.
Do You Need Help With An Excel Problem?.
Finally, I am pleased to announce I have teamed up with Excel Rescue, where you can get help FAST. Why not check it out?.
