Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
setting print range | Excel Programming | |||
Setting print range using VBA | Excel Programming | |||
Setting print range using VBA | Excel Programming | |||
Setting print range using VBA | Excel Programming | |||
Setting Print range | Excel Programming |