TheSmallman.com

an XL ideas Lab

  • Home
    • Excel Dashboard Course
    • Financial Modelling Course
    • Excel Infographics Course
    • Excel VBA Course
  • Models
  • Native Excel
  • Charts
  • Dashboards
  • Infographics
  • Modelling
  • VBA
  • Blog
  • About
Menu

Excel Dashboards VBA and more

Street Address
City, State, Zip
Phone Number
an XL ideas Lab

Your Custom Text Here

Excel Dashboards VBA and more

  • Home
  • Courses
    • Excel Dashboard Course
    • Financial Modelling Course
    • Excel Infographics Course
    • Excel VBA Course
  • Models
  • Native Excel
  • Charts
  • Dashboards
  • Infographics
  • Modelling
  • VBA
  • Blog
  • About

Excel Change Print Area with VBA

May 12, 2016 Marcus Small

Setting the print area on a moving range can be a difficult and frustrating thing to trap.  You may wish to trap a specific range with your print area or have the print range only consider certain columns.  Let's say you have a couple of helper columns in an Excel workbook and don't want these columns to be printed but you want the columns to be visible.  You could manually set the print area to exclude the print area but now you have more data which you have added and you would like the print area to update in kind. 

The following VBA procedure will generate a print area which considers columns A to D.

Option Explicit

Sub PrintArea()
Dim sh as Worksheet
Set sh = Sheet1

   sh.PageSetup.PrintArea =sh.Range("A1", sh.Range("D65536").End(xlUp)).Address
End Sub

The above will trap the last used range in column D.  Make sure the sheet you are using it on is changed.

Sheet1 is the worksheet code name.  This needs to be adjusted if you are using the code on a sheet other than sheet1. 

Oh this is set up for Excel 2003 so here is an update for Excel 2007 and above. 

Option Explicit

Sub PrintArea1()
Dim sh as Worksheet
Set sh = Sheet1

   sh.PageSetup.PrintArea = sh.Range("A1", sh.Range("D" & rows.count).End(xlUp)).Address
End Sub

Why does it work?  Well the only range which is considered in the print area is from A1 to the last used row in Column D.

What if you wanted it to be the current region.  Perhaps something like the following.

Sub testo1()
   Sheet1.PageSetup.PrintArea = [A1].CurrentRegion.Address
End Sub

Where all of the data in the 'block' of data will be considered part of the print area.  This sort of code could be added to a change event so the macro would not have to be run over and again to make it fire. 

Tags Excel, Print, area, vba, set
← Open Excel Update FileRead Individual Columns to an Array →

Featured Posts

Evaluate and Filter Part 2

Evaluate and Filter Part 2
April 26, 2015

 Showing Trends with a Chart

Showing Trends with a Chart
March 5, 2015

Excel VBA to Sort an Array

Excel VBA to Sort an Array
February 23, 2015

 Excel VBA Slicer Selection

Excel VBA Slicer Selection
January 15, 2015

Recent Posts

Protect a Worksheet, Use the Autofilter

Protect a Worksheet, Use the Autofilter October 18, 2018

Countifs Across Sheets

Countifs Across Sheets October 10, 2018

Excel VBA to Select Sheets

Excel VBA to Select Sheets September 22, 2018

Power BI Desktop Dashboard

Power BI Desktop Dashboard August 20, 2018

New Excel Dashboard Tools

New Excel Dashboard Tools August 1, 2018

Watch This Space

Watch This Space July 11, 2018

The Fuzzy World of Financial Modelling

The Fuzzy World of Financial Modelling May 31, 2018

Excel Autofill - Fill Down to Bottom of Range

Excel Autofill - Fill Down to Bottom of Range April 8, 2018

Social Media Dashboard in Excel

Social Media Dashboard in Excel April 5, 2018

Range Areas in Excel with VBA

Range Areas in Excel with VBA March 15, 2018

 

TrainiNg

  • Dashboarding with Excel
  • Visual Analytics
  • Advanced Dashboard Design in Excel
  • Financial Modelling in Excel
  • Advanced Financial Modelling in Excel

 

  • 3 Statement Modelling in Excel
  • Project Financial Modelling
  • PowerPivot
  • Excel Automation with VBA
  • Financial Modelling with VBA

Follow US:

 

marcussmall@thesmallman.com
 

TheSmallman.com - Making your small systems hum...
© Copyright theSmallman.com All Rights Reserved.