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