VBA Excel Pagesetup Printarea festlegen

[gabneo]

selber gelöst

Delphi-Quellcode:

Public Sub Workbook_BeforePrint(Cancel As Boolean)
'Quelle: Kolja Aidam IT-Services Copyright 2009

Dim xelRows As Long, i As Long

Application.ScreenUpdating = False

On Error Resume Next

With ActiveSheet

 .ResetAllPageBreaks
 For xelRows = 1 To Range("A65536").End(xlUp).Row
 If Rows(xelRows).Hidden = False Then i = i + 1
 If i Mod 76 = 0 Then .HPageBreaks.Add Cells(xelRows + 1, 1)
 Next

 .PageSetup.PrintArea = "$A$1:$S$38076"
 
 .VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
 Set .HPageBreaks(1).Location = Range("A77")

End With

Application.ScreenUpdating = True
    
End Sub

greez