print areas in vba

[markh1182]

Hi,

I have this code that sets the print area. Is there a way to amend slightly so it says that each page should have 59 rows from the excel spreadsheet? This is because it is putting page breaks in weird places, and not just sticking to the page setup format, from the looks of it.

Ta,
Mark

Sub PrintArea()

bottom = ActiveSheet.Range("N65536").End(xlUp).Row

    Cells.Select
    ActiveSheet.PageSetup.PrintArea = ""
    
    Range("A1").Select
    
MyRange = ("I1" & ":" & "O" & bottom)

ActiveSheet.PageSetup.PrintArea = MyRange


End Sub

 

[erik.van.geit]

Hi, Mark,

struggling for years with this from time to time, always feeling to need workarounds
why you cannot simply remove all those stupid automatic pagebreaks and put some yourself, is a mistery for me

this seems to work

Sub test()
Dim pb As HPageBreak
Dim i As Long

    For i = 1 To ActiveSheet.HPageBreaks.Count
    ActiveSheet.HPageBreaks(i).Location = Cells(i * 59, 1)
    Next i

End Sub

kind regards,
Erik