Option Explicit
Const cstrSheets As String = "Jan;Febr;März;April;Mai;Juni;Juli;August;Sept;Okt;Nov;Dez"
Sub P()
Dim rng As Range
Dim n As Integer
Dim blnHidden As Boolean
Set rng = Range("B43:U73")
Application.ScreenUpdating = False
If Not blnHidden Then
blnHidden = True
For n = 1 To rng.Rows.Count
If Application.CountA(rng.Rows(n)) = 0 Then rng.Rows(n).Hidden = blnHidden
Next
For n = 1 To rng.Columns.Count
If Application.CountA(rng.Columns(n)) = 0 Then rng.Columns(n).Hidden = blnHidden
Next
Else
blnHidden = False
rng.Rows.Hidden = blnHidden
rng.Columns.Hidden = blnHidden
End If
Application.ScreenUpdating = True
End Sub
Sub Q()
vntSheets = Split(cstrSheets, ";")
For lngIndex = 0 To UBound(vntSheets)
Sheets(vntSheets(lngIndex)).Range("B43:U73").EntireRow.Hidden = False
Sheets(vntSheets(lngIndex)).Range("B43:U73").EntireColumn.Hidden = False
Next
End Sub
Der zweite Code blendet alle Zeilen in einem definierten Bereich für definierte Sheets aus und wieder ein.
Option Explicit
Const cstrSheets As String = "Jan;Febr;März;April;Mai;Juni;Juli;August;Sept;Okt;Nov;Dez"
Sub aus()
Dim lngLast As Long, rngHide As Range
Dim vntSheets As Variant, lngIndex As Long
vntSheets = Split(cstrSheets, ";")
For lngIndex = 0 To UBound(vntSheets)
If rngHide Is Nothing Then
With Sheets(vntSheets(lngIndex))
lngLast = Evaluate("MAX(IF('" & .Name & "'!B43:U73<>"""",ROW(43:73)))")
If lngLast = 0 Then lngLast = 24
On Error Resume Next
Set rngHide = .Range("B43:B" & lngLast).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
End With
End If
If Not rngHide Is Nothing Then Sheets(vntSheets(lngIndex)).Range(rngHide.Address).EntireRow.Hidden = True
Next
Set rngHide = Nothing
End Sub
Sub ein()
Dim vntSheets As Variant, lngIndex As Long
vntSheets = Split(cstrSheets, ";")
For lngIndex = 0 To UBound(vntSheets)
Sheets(vntSheets(lngIndex)).Range("B43:U73").EntireRow.Hidden = False
Next
End Sub
Ich möchte jetzt Code 1 so in Code 2 integrieren (oder andersherum ;)), dass für den Bereich B43:U73 in allen Tabellenblättern alle leeren Zeilen und Spalten ausgeblendet werden.
Danke schon mal im Voraus :)