Defining Print Area

The following two lines are equivalent

Activesheet.PageSetup.PrintArea = "" 
Activesheet.Names("Print Area").Delete

Activesheet.VPageBreaks(1).Location = Worksheets(1).Range("B5") 
Activesheet.VPageBreaks(1).DragOff Direction:=xlDirection.xlToRight RegionIndex:=1

VPageBreaks Collection

A collection of vertical page breaks within the print area.
Each vertical page break is represented by a VPageBreak object
Use the Add method to add a vertical page break.

Activesheet.VPageBreaks.Add Before:=ActiveCell 

If you add a page break that does not intersect the print area, then the new VPageBreak object will not appear in the VPageBreaks collection for that sheet.

For an automatic print area, the VPageBreaks property applies only to the page breaks within the print area.

For a user-defined print area of the same range, the VPageBreaks property applies to all the page breaks.

VPageBreaks(1).DragOff (Direction, RegionIndex)

Direction - The direction to drag the page break
RegionIndex - The print area region index for the page break.If the print area is continuous then there is only one print region. If the print area is non continous then there is more than one.

This method drags a page break off the print area.
You have to be in print preview mode for this to work
If you are not in page page preview then an error will be generated.

ActiveWindow.View = xlPageBreakPreview 
Activesheet.VPageBreaks(1).DragOff Direction:=xlDirection.xlToRight RegionIndex:=1
ActiveWindow.View = xlNormalView

HPageBreaks Collection

Whenever you use / change the print area of a worksheet by dragging the blue lines in "page break" view, using activesheet.Hpagebreaks(1).dragoff direction, regionindex you will get a Dr Watson if the page breaks to does not exist.

How many pages will be printed ?

To determine the number of pages that will be printed for the active worksheet use:
This is an XLM (Excel 4) macro.

Public Sub ShowPageCount() 
Dim ipagecount As Integer
Dim ipages As Integer
Dim objwsh as Worksheet

   ipagecount = 0
   For Each objwsh In Worksheets
      ipages = ExecuteExcel4Macro("Get.Document(50)")
      ipagecount = ipagecount + ipages
   Next objwsh
End Sub

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext