# [SOLVED] Excel MACRO to print range



## ats1025 (Apr 6, 2011)

I would like to add a button a sheet in my spreadsheet that will print a certain range (B7153), but also print the range at 65% to fit on a piece of paper. (If it's not printed at 65%, the table prints across multiple pieces of paper.

I want to make a button so each user in the shared workbook does not have to manually set the print options.

Also, can you specify that the sheet print on a 11"x14" rather than 8-1/2" x 11" piece of paper?

Thanks!!!


----------



## ats1025 (Apr 6, 2011)

*Re: Excel MACRO to print range*

In addition, is there a way to set the print area in the macro? I can manually set it, but I want to reset the print area to include only those rows that contain data. All of the rows (from row 8 to 160) contain lookup formulas for data on other sheets; however, I only want the print area to include those cells with data.

Note: I have an ISERROR part to each formula so all #NEW! and #VALUE! display as blanks. So, I am not sure if that would be detected as an empty cell or not.

Thanks!


----------



## macropod (Apr 11, 2008)

*Re: Excel MACRO to print range*

Hi ats,

You can do this without a macro. Simply use the page setup tools to set the print area, paper size and a 'fit to' setting of 1 page wide & high. If you save the workbook, these settings will be saved with it.

If you feel there is a need to enforce thse settings, you could use a macro like the following in the workbook's 'ThisWorkbook' module:

```
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
  On Error Resume Next
  .PrintArea = "B7:P153"
  .LeftMargin = Application.InchesToPoints(0.75)
  .RightMargin = Application.InchesToPoints(0.75)
  .TopMargin = Application.InchesToPoints(0.75)
  .BottomMargin = Application.InchesToPoints(0.75)
  .HeaderMargin = Application.InchesToPoints(0.375)
  .FooterMargin = Application.InchesToPoints(0.375)
  .CenterHorizontally = True
  .CenterVertically = True
  .Orientation = xlLandscape
  '.PaperSize = xlPaperFanfoldUS 'U.S. Standard Fanfold (14-7/8 in. x 11 in.)
  .PaperSize = xlPaper10x14
  .BlackAndWhite = False
  .Zoom = False
  .FitToPagesWide = 1
  .FitToPagesTall = 1
End With
End Sub
```
You'll note that I've specified a 10x14 paper size. I've also left in a commented line for an 14-7/8x11 paper size. Your's is probably one of these. The 'On Error' line is there in case the printer actually used (eg a student's home printer) doesn't support all these settings. The code also specifies 3/4in margins all round and allows color. You may want to change these too.


----------



## macropod (Apr 11, 2008)

*Re: Excel MACRO to print range*

Hi ats,

Re the second question, you could query the row # for the last row whose value isn't "", then pass that as part of the range specification.


----------



## macropod (Apr 11, 2008)

*Re: Excel MACRO to print range*

Hi ats,

Based on what you've posted in your other threads, try:

```
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
With Worksheets("Search---Courses")
  For i = 9 To 155
    If Not IsNumeric(.Range("Q" & i)) Then Exit For
  Next
  With .PageSetup
    On Error Resume Next
    .PrintArea = "B7:P" & i - 1
    .LeftMargin = Application.InchesToPoints(0.75)
    .RightMargin = Application.InchesToPoints(0.75)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.375)
    .FooterMargin = Application.InchesToPoints(0.375)
    .CenterHorizontally = True
    .CenterVertically = True
    .Orientation = xlLandscape
    '.PaperSize = xlPaperFanfoldUS 'U.S. Standard Fanfold (14-7/8 in. x 11 in.)
    .PaperSize = xlPaper10x14
    .BlackAndWhite = False
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
  End With
End With
End Sub
```


----------



## ats1025 (Apr 6, 2011)

*Re: Excel MACRO to print range*

Worked perfectly. Thanks so much for your ideas!


----------

