Macro creates a new Excel Worksheet, but it prints too many pages 1

[RyanScharfy]

My macro creates a worksheet from other data in Excel. When it prints, I'd like it to go the length of the list of subtotaled data, but it keeps going for many blank pages. Any easy code fix? My printing code I put in is as follows:

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.5)
.RightMargin = Application.InchesToPoints(0.5)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 75
.FitToPagesWide = False
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With

 

[PHV]

Perhaps the PageSetup.PrintArea property ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

You have DATA in that area...

could be character that you can see or NOT if the font is the same as the background

could be formatting - borders, shading

To be rid of it you must select all the rows below the datat area and DELETE -- shift whatever.

NOW try your PRINT.

Skip,

_[red][/red]

 

[RyanScharfy]

Hi Skip,

Deleting the data after my list in both the rows and columns doesn't change anything unless I save the worksheet to my hard-drive.

I wouldn't expect the users to save it to their hard-drive, but fully expect them to print it.

Maybe I could somehow capture the the data and make it a print area. I tried recording a macro but could only get the following...

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$C$8:$Q$54"

That print area is going to be variable, not always from C to Q.

Any way to do a variable print area?

 

[SkipVought]

Is the print area CONTIGUOUS?

ActiveSheet.PageSetup.PrintArea = activesheet.usedrange.currentregion.address

Skip,

_[red][/red]

 

[SkipVought]

If you have a non-contiguous print area

Sub test()
    Dim r1, c1, r2, c2, rng
    With ActiveSheet
        Set rng = .Cells.Find("*")
        With rng
            r1 = .Row
            c1 = .Column
        End With
        Set rng = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), , , , xlPrevious)
        With rng
            r2 = .Row
            c2 = .Column
        End With
        .PageSetup.PrintArea = .Range(.Cells(r1, c1), .Cells(r2, c2)).Address
        
    End With
    set rng = nothing
End Sub

Skip,

_[red][/red]

 

[RyanScharfy]

Skip, your code got me thinking and next thing you know, I got this duct tape code to work. Voila, no more problem.

' Disable Alert and Set Print Area so that extra blank pages aren't printed.
Application.DisplayAlerts = False
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = ActiveSheet.UsedRange.CurrentRegion.Address
ActiveSheet.PageSetup.PrintArea = ""
Application.DisplayAlerts = True

 

[RyanScharfy]

FYI to anybody who see's this... Skip's non-contiguous code solved my problem. The "ductape" solution above wouldn't work for another project I had, but I cut and pasted Skip's code in and it solved the problem.