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]

Can anyone else shed some light on this one?

It prints landscape fine, but wont fit to 1 page...

 

[thorpyuk]

Hi,

Can anyone shed some light on why my code isn't working superbly!

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

It prints, landscape, but without fitting to 1 page only...