Setting Print Range
 

I want to set the print range via VB so that it prints columns A-K via
landscape across, then prints only till the last row of data (checking
column A). I've tried the following, but it still prints all the way
down to the end.

'Set Print Area and Change to Landscape

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$638"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

'Sets Print Range

Dim Rng As Range
Dim LastCell As Range
Set Rng = Range("A1:K1") ' the address of first row of data you
want to
Set LastCell = Rng.EntireColumn.Find("*", Rng.Cells(1), , , ,
xlPrevious)
Rng.Resize(LastCell.Row - Rng.Row + 1).Name = "Print_Area"

ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight,
RegionIndex:=1
ActiveWindow.View = xlNormalView

Any help is greatly appreciated.

Brendon

Setting Print Range
 

Sorry, I figured it out. Here's how I accomplished it.

Dim lastr As Long
lastr = Sheets("REQ LOG").Range("A" & Rows.Count).End(xlUp).Row
Sheets("REQ LOG").PageSetup.PrintArea = "$A$1:$K$" & lastr

ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight,
RegionIndex:=1
ActiveWindow.View = xlNormalView


On Dec 2, 10:56*am, Fester wrote:
I want to set the print range via VB so that it prints columns A-K via
landscape across, then prints only till the last row of data (checking
column A). *I've tried the following, but it still prints all the way
down to the end.

'Set Print Area and Change to Landscape

* * With ActiveSheet.PageSetup
* * * * .PrintTitleRows = ""
* * * * .PrintTitleColumns = ""
* * End With
* * ActiveSheet.PageSetup.PrintArea = "$A$1:$D$638"
* * With ActiveSheet.PageSetup
* * * * .LeftHeader = ""
* * * * .CenterHeader = ""
* * * * .RightHeader = ""
* * * * .LeftFooter = ""
* * * * .CenterFooter = ""
* * * * .RightFooter = ""
* * * * .LeftMargin = Application.InchesToPoints(0.75)
* * * * .RightMargin = Application.InchesToPoints(0.75)
* * * * .TopMargin = Application.InchesToPoints(1)
* * * * .BottomMargin = Application.InchesToPoints(1)
* * * * .HeaderMargin = Application.InchesToPoints(0.5)
* * * * .FooterMargin = Application.InchesToPoints(0.5)
* * * * .PrintHeadings = False
* * * * .PrintGridlines = False
* * * * .PrintComments = xlPrintNoComments
* * * * .PrintQuality = 600
* * * * .CenterHorizontally = False
* * * * .CenterVertically = False
* * * * .Orientation = xlLandscape
* * * * .Draft = False
* * * * .PaperSize = xlPaperLetter
* * * * .FirstPageNumber = xlAutomatic
* * * * .Order = xlDownThenOver
* * * * .BlackAndWhite = False
* * * * .Zoom = 100
* * * * .PrintErrors = xlPrintErrorsDisplayed
* * End With

'Sets Print Range

* * Dim Rng As Range
* * Dim LastCell As Range
* * Set Rng = Range("A1:K1") ' the address of first row of data you
want to
* * Set LastCell = Rng.EntireColumn.Find("*", Rng.Cells(1), , , ,
xlPrevious)
* * Rng.Resize(LastCell.Row - Rng.Row + 1).Name = "Print_Area"

* * ActiveWindow.View = xlPageBreakPreview
* * ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight,
RegionIndex:=1
* * ActiveWindow.View = xlNormalView

Any help is greatly appreciated.

Brendon

Setting Print Range
 

In the line
Set LastCell = Rng.EntireColumn.Find("*", Rng.Cells(1), , , ,
xlPrevious)

you do not specify most of the arguments of find. That means that it could
be using xlValues or xlFormulas depending on how it was set last. If you have
formulas in the range then it will find the last formula even if it is
returning blank. With Find it is very important to understand all of the
arguments and to ensure that you specify the ones that are necessary...

--
HTH...

Jim Thomlinson


"Fester" wrote:

I want to set the print range via VB so that it prints columns A-K via
landscape across, then prints only till the last row of data (checking
column A). I've tried the following, but it still prints all the way
down to the end.

'Set Print Area and Change to Landscape

With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$638"
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With

'Sets Print Range

Dim Rng As Range
Dim LastCell As Range
Set Rng = Range("A1:K1") ' the address of first row of data you
want to
Set LastCell = Rng.EntireColumn.Find("*", Rng.Cells(1), , , ,
xlPrevious)
Rng.Resize(LastCell.Row - Rng.Row + 1).Name = "Print_Area"

ActiveWindow.View = xlPageBreakPreview
ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight,
RegionIndex:=1
ActiveWindow.View = xlNormalView

Any help is greatly appreciated.

Brendon