Programming to set print area and resize rows

**prawer** · 08-28-2009, 05:32 PM

Hi All,

I have a workbook containing a sheet that lists several possible line items in a price quote, and a pivot table containing those items selected by the user to be displayed (using checkboxes and a report filter).

I want my users to have to do as little as possible to go from selecting the rows to include, to printing to pdf. As part of this, I'm wondering if there is a way to do two things:

1. Automate the print area setting, and force to print to 1 page wide by 1 page tall
2. Set the height of blank rows appearing between items to 1

In the attached example, the code would not include the report filter row (row 2), resize rows 7, 9, 12, 14, 17, 19, 21, 24, 26, 28, 31, 33 to 1, and set the print area to A3:D33, on the basis of the fact that these rows include data.

I have 0 programming experience but would love to learn how to make this happen. Thanks in advance for anyone who can help with this ...

**Leith Ross** · 08-28-2009, 06:22 PM

Hello prawer,

I don't have Excel 2007, but this macro should work. This will set the Print Area for the ActiveSheet to "A3:D33". The rows you listed will be changed to a height of 1. The sheet will then print out.

Code:

Sub PrintoutSheet()

  Dim Wks As Worksheet
  
    Set Wks = ActiveSheet
    
    With Wks.PageSetup
      .PrintArea = "A3:D33"
      .FitToPagesTall = 1
      .FitToPagesWide = 1
    End With
    
      For Each Row In Array(7, 9, 12, 14, 17, 19, 21, 24, 26, 28, 31, 33)
        Wks.Rows(Row).RowHeight = 1
      Next Row
    
    Wks.PrintOut
    
End Sub

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

**prawer** · 08-28-2009, 06:27 PM

Leith,

Thanks very much for your response. Unfortunately the settings I gave in my original post were examples -- I'd want the macro to identify the appropriate print area and then set it (ie it won't always be a3:d33 -- that depends on what the user has selected for inclusion from the source data. Likewise the row #s.

By the way, I messed around with the macro recorder and it included this bit of code on the row resize step:

Code:

ActiveSheet.PivotTables("PivotTable4").PivotSelect _
        "Service[All;'Blank'] 'TRUE'", xlDataAndLabel, True
Selection.RowHeight = 1

Will that work regardless of which rows have been selected for inclusion?

**Leith Ross** · 08-28-2009, 08:20 PM

Hello prawer,

Do you want to set the print area based the user's selection in the pivot table?

**prawer** · 08-28-2009, 08:26 PM

Not really, if you mean the cells that the user has selected. I have some very technologically shaky sales reps, so I want the code to set the print area to include the row labels of the pivottable (the part to the left of 'data items', which my pivot doesn't have) plus a few rows above (to separate it from my company logo, which is on top).

Is there a way to refer to the array containing the row labels of the pivot table in VBA?

**prawer** · 08-31-2009, 07:01 PM

Hi All. I hate to 'bump' but I know this is possible, I just don't have the programming chops to make it happen. I'm at the point where I know how to select the row labels in a pivot table, but I don't know how to edit the array reference to 'bump it up' a few rows ...