Setting Print Range

[Fester]

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

 

[Fester]

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

 

[Jim Thomlinson]

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...