VBA /macro to print only visible data

[thorpyuk]

Hi Guys,

I have a sheet thats created automatically from a load of filtered data. Unfortunately, this does mean that there's loads of 'empty' cells that seem to be picked up as valid, meaning that any print preview will show my data bunched up in the corner!

Does anyone have any code that will 1) auto-set the print area to only cells that have data in them, and 2) automatically select landscape layout + fit to 1 page? (all my data should go on 1 page always)

Thanks!

 

[thorpyuk]

Any code at all for printing will be grand to start me off? Thanks

 

[thorpyuk]

any takers?

where's that penguin when you need him...

 

[thorpyuk]

Never got anywhere with this.... can anyone help?

Thanks!

 

[VoG]

Try

Sub test()
    With ActiveSheet.PageSetup
        .PrintArea = ActiveSheet.UsedRange.Address
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    ActiveSheet.PrintOut
End Sub

 

[thorpyuk]

Hi Vog,

Thanks for your reply.

It was a partial successs..... the page does indeed orientate itself correctly, however it still tries to print loads of blank pages.....

Do you have code to print selection? If so, I can use:

    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select

And print using that method....

Also, is there any way I can select a printer other than the default printer?
Thanks!

 

[VoG]

    Selection.PrintOut

but I don't think that this will do anything different.

To select a different printer:

Dim bresponse as Boolean
bResponse = Application.Dialogs(xlDialogPrinterSetup).Show
If bResponse = False Then
    MsgBox "User cancelled"
    Exit Sub
End If

 

[thorpyuk]

I'm so close! I think it just needs a bit of re-jigging....

Private Sub CommandButton1_Click()
    With ActiveSheet.PageSetup
        '.PrintArea = ActiveSheet.UsedRange.Address
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveSheet.PageSetup.PrintArea = Selection.Address
    ActiveSheet.PrintOut
Range("A1").Select
End Sub

This works! The only thing it doesn't do is print on 1 page. It now prints on 2 pages width-wise, as if it's not picking up the .fittopageswide = 1 properly...

I've probably got stuff in there I dont need now as well!
Any ideas what I need to change? Thanks for your help by the way, it's nice to see a fellow brit on here!

 

[VoG]

Perhaps change the order:

Private Sub CommandButton1_Click()
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With ActiveSheet.PageSetup
        .PrintArea = Selection.Address
        .Orientation = xlLandscape
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
    ActiveSheet.PrintOut
    Range("A1").Select
End Sub

 

[thorpyuk]

No, same thing :/ prints landscape but wont fit correctly on 1 page, spreads over 3 wide, and if there's too much going down, prints only a set amount of rows too....