Print Question?

Todd_M · Apr 29th, 2002, 10:36 AM

Hi-
I have a sheet that the user can print by clicking a commandbutton.

Range("A1:I160").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$160"
With ActiveSheet.PageSetup
.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)
.PrintComments = xlPrintNoComments
.PrintQuality = -3
.Orientation = xlLandscape
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver

End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
end sub

I set the rows to be printed to row 160. My question is if there is only data to fill one page (up to row 35) and the rest of the print range contains empty cells, how do you write a code that will stop the printer from printing the last blank 4 sheets. This sheet im printing at times may contain 10 rows of data or 150 rows of data at anytime.

Mark O'Brien · Apr 29th, 2002, 10:39 AM

Is the data that you want to print the only data on the worksheet?

If it is then you could replace:



Range("A1:I160").Select

With



Activesheet.UsedRange.Select

HTH

Todd_M · Apr 29th, 2002, 03:04 PM

Thanks Mark, sorry it to so long to respond. The Activesheet.UsedRange.Select works fine, however in column M, I have text that goes down to Row 180, and when I apply this code the selection highlights range A1:M180. I only want the usedrange.selection to set a print area for any used cells in rangeA1:I160, then print out only as many pages as needed.

Apr 29th, 2002, 10:39 AM	#2
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Is the data that you want to print the only data on the worksheet? If it is then you could replace: Range("A1:I160").Select With Activesheet.UsedRange.Select HTH __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 29th, 2002, 10:36 AM	#1
Todd_M Board Regular Join Date: Feb 2002 Posts: 117	Hi- I have a sheet that the user can print by clicking a commandbutton. Range("A1:I160").Select ActiveSheet.PageSetup.PrintArea = "$A$1:$I$160" With ActiveSheet.PageSetup .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) .PrintComments = xlPrintNoComments .PrintQuality = -3 .Orientation = xlLandscape .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver End With ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True end sub I set the rows to be printed to row 160. My question is if there is only data to fill one page (up to row 35) and the rest of the print range contains empty cells, how do you write a code that will stop the printer from printing the last blank 4 sheets. This sheet im printing at times may contain 10 rows of data or 150 rows of data at anytime.

Apr 29th, 2002, 03:04 PM	#3
Todd_M Board Regular Join Date: Feb 2002 Posts: 117	Thanks Mark, sorry it to so long to respond. The Activesheet.UsedRange.Select works fine, however in column M, I have text that goes down to Row 180, and when I apply this code the selection highlights range A1:M180. I only want the usedrange.selection to set a print area for any used cells in rangeA1:I160, then print out only as many pages as needed.