Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Print Question?

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •