Print Area and Printing

[ASH2012]

Hi All,

I want to set a conditional print area via a macro run from a button and print<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>


<o></o>

• <LI style="mso-list: l0 level1 lfo1" class=MsoNormal>It is to print in the <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on"><st1:PlaceType w:st="on">Range</st1:PlaceType> of <st1:PlaceName w:st="on">A3</st1:PlaceName></st1lace>:P800<o></o> <LI style="mso-list: l0 level1 lfo1" class=MsoNormal>It is only to print a line when an entry has been made in Column A<o></o> <LI style="mso-list: l0 level1 lfo1" class=MsoNormal>The print button activates the print window where they can say okay or select their printer<o></o>
• When it does print it should be in landscape and made to fit by ‘x‘ amount tall by 1 wide<o></o>

<o></o>
Hope you can help J<o></o>

 

[pbornemeier]

I believe this will work:

Option Explicit

Sub ConditionalPrint()
    With ActiveSheet
        If .Cells(.Rows.Count, 1).End(xlUp).Row = 1 And Trim(.Range("A1").Value) = 0 Then GoTo End_Sub
        With .PageSetup
            .PrintArea = "$A$3:$P$800"
            .FitToPagesWide = 1
            .FitToPagesTall = False
        End With
    End With
    Application.Dialogs(xlDialogPrint).Show
End_Sub:
End Sub

 

[ASH2012]

Hi,

Thanks for the response,

I have put the coding in and run it, I'm probably missing something, but when I preview it's not 1 page wide and there are multiple blank lines and blank pages too.

Again thanks for your help it is much appreciated.

 

[pbornemeier]

By the way, Welcome to the Board!

Try this modified code:

Sub ConditionalPrint()
    With ActiveSheet
        If .Cells(.Rows.Count, 1).End(xlUp).Row = 1 And Trim(.Range("A1").Value) = 0 Then GoTo End_Sub
        With .PageSetup
            .PrintArea = "$A$3:$P$800"
            .Orientation = xlLandscape
            .Zoom = False 'Must be set to False or ".FitToPagesTall = False" is ignored
            .FitToPagesWide = 1
            .FitToPagesTall = False
        End With
    End With
    Application.Dialogs(xlDialogPrint).Show
End_Sub:
End Sub

.Zoom = False should ensure a single page across.
The code in my initial response neglected the Landscape requirement. I have corrected it with this code.

As far as blank lines and pages, when you define a print area, all of that area is printed, even if it is blank. If you want to avoid blank lines between row 3 and 800 (you will have to either (manually or via code) find them and hide them. If you want to avoid blank lines/pages after the last populated row, you can either find and hide them or find the last populated row and adjust the $P$800 to reflect the last populated row.