Hallo liebe Leute,
gint es eine Möglichkeit per VBA Excel zu sagen, dass es ausgeblendete Zeilen mitdrucken soll?
Viele Grüße,
Basti
Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Call SelectComplement(Tabelle1)
Application.OnTime Now + TimeSerial(0, 0, 1), "Event_After_Print"
End Sub
Option Explicit
Dim rngNot_Visible As Range
'********************************************************
'Code ist von Erich aus Kamp-Lintfort *
'********************************************************
Sub SelectComplement(oSh As Worksheet)
Dim rngX As Range, rngC As Range, rngE As Range
Set rngNot_Visible = oSh.Cells.SpecialCells(xlCellTypeVisible).EntireRow
For Each rngX In rngNot_Visible.Areas
Set rngC = ComplementRect(rngX)
If Not rngC Is Nothing Then
If rngE Is Nothing Then Set rngE = rngC Else Set rngE = Intersect(rngE, rngC)
End If
Next rngX
If Not rngE Is Nothing Then
Set rngNot_Visible = rngE
rngNot_Visible.EntireRow.Hidden = False
End If
End Sub
Function ComplementRect(rngA As Range) As Range
Dim zv As Long, zb As Long, sv As Long, sb As Long, rngT As Range
zv = rngA.Row
zb = zv + rngA.Rows.Count - 1
sv = rngA.Column
sb = sv + rngA.Columns.Count - 1
If zv > 1 Then Set rngT = Range(Rows(1), Rows(zv - 1))
If zb < Rows.Count Then
If rngT Is Nothing Then
Set rngT = Range(Rows(zb + 1), Rows(Rows.Count))
Else
Set rngT = Union(rngT, Range(Rows(zb + 1), Rows(Rows.Count)))
End If
End If
If sv > 1 Then
If rngT Is Nothing Then
Set rngT = Range(Cells(zv, 1), Cells(zb, sv - 1))
Else
Set rngT = Union(rngT, Range(Cells(zv, 1), Cells(zb, sv - 1)))
End If
End If
If sb < Columns.Count Then
If rngT Is Nothing Then
Set rngT = Range(Cells(zv, sb + 1), Cells(zb, Columns.Count))
Else
Set rngT = Union(rngT, Range(Cells(zv, sb + 1), Cells(zb, Columns.Count)))
End If
End If
Set ComplementRect = rngT
End Function
Sub Event_After_Print()
If Not rngNot_Visible Is Nothing Then
rngNot_Visible.EntireRow.Hidden = True
Set rngNot_Visible = Nothing
End If
End Sub