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.
