n. gesperrte Zellen leeren

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Option Explicit Sub Jekt() Dim chkCell As Range, rngC As Range With ActiveSheet For Each chkCell In .Range("B9:I9") With chkCell If .Value = "" Then .EntireColumn.Hidden = True For Each rngC In .EntireColumn If Not rngC.Locked Then rngC.ClearContents Next rngC Else .EntireRow.Hidden = False .EntireColumn.Hidden = False End If End With Next chkCell End With End Sub

Hi Erich hab mal versucht deinen Vorschlag in meinen Code einzuarbeiten, leider bekomm ich eine Fehlermeldung. Leider reichen da meine Kenntnisse nicht ganz um zu erkennen woran es liegt. Hier der vollständige Code:


Sub Zeilen_Spalten_Gewichtung_ausblenden()
Dim wksChk As Worksheet
Dim i As Long, n As Long
Dim chkCell As Range
Dim rngC As Range
Dim wksArr(9) As String
wksArr(0) = "Gewichtung1"
wksArr(1) = "Gewichtung2"
wksArr(2) = "Gewichtung3"
wksArr(3) = "Gewichtung4"
wksArr(4) = "Gewichtung5"
wksArr(5) = "Gewichtung6"
wksArr(6) = "Gewichtung7"
wksArr(7) = "Gewichtung8"
wksArr(8) = "Gewichtung9"
For i = 0 To UBound(wksArr()) - 1
With Worksheets(wksArr(i))
'Spalte A
For n = 10 To 17
If .Cells(n, 1) = "" Then
.Rows(n).EntireRow.Hidden = True
Else
.Rows(n).EntireRow.Hidden = False
End If
Next n
'Spalten ausblenden
For Each chkCell In .Range("B9:I9")
If chkCell = "" Then
chkCell.EntireColumn.Hidden = True
For Each rngC In .EntireColumn
If Not rngC.Locked Then rngC.ClearContents
Next rngC
Else
chkCell.EntireRow.Hidden = False
chkCell.EntireColumn.Hidden = False
End If
Next chkCell
End With
Next i
End Sub

Sub Zeilen_Spalten_Gewichtung_ausblenden() Dim wksChk As Worksheet Dim i As Long, n As Long Dim chkCell As Range Dim rngC As Range Dim wksArr(9) As String wksArr(0) = "Gewichtung1" wksArr(1) = "Gewichtung2" wksArr(2) = "Gewichtung3" wksArr(3) = "Gewichtung4" wksArr(4) = "Gewichtung5" wksArr(5) = "Gewichtung6" wksArr(6) = "Gewichtung7" wksArr(7) = "Gewichtung8" wksArr(8) = "Gewichtung9" For i = 0 To UBound(wksArr()) - 1 With Worksheets(wksArr(i)) 'Spalte A For n = 10 To 17 If .Cells(n, 1) = "" Then .Rows(n).EntireRow.Hidden = True Else .Rows(n).EntireRow.Hidden = False End If Next n 'Spalten ausblenden For Each chkCell In .Range("B9:I9") If chkCell = "" Then chkCell.EntireColumn.Hidden = True For Each rngC In .EntireColumn If Not rngC.Locked Then rngC.ClearContents Next rngC Else chkCell.EntireRow.Hidden = False chkCell.EntireColumn.Hidden = False End If Next chkCell End With Next i End Sub

Option Explicit Sub Zeilen_Spalten_Gewichtung_ausblenden() Dim i As Long, n As Long Dim chkCell As Range, rngC As Range For i = 1 To 9 With Worksheets("Gewichtung" & CStr(i)) 'Spalte A For n = 10 To 17 If .Cells(n, 1) = "" Then .Rows(n).EntireRow.Hidden = True Else .Rows(n).EntireRow.Hidden = False End If Next n 'Spalten ausblenden For Each chkCell In .Range("B9:I9") If chkCell = "" Then chkCell.EntireColumn.Hidden = True For Each rngC In chkCell.EntireColumn ' hier fehlte chkCell If Not rngC.Locked Then rngC.ClearContents Next rngC Else chkCell.EntireRow.Hidden = False chkCell.EntireColumn.Hidden = False End If Next chkCell End With Next i End Sub

Sub Zeilen_Spalten_Gewichtung_ausblenden() Dim i As Long, n As Long Dim chkCell As Range, rngC As Range For i = 1 To 2 With Worksheets("Gewichtung" & CStr(i)) 'Spalte A For n = 10 To 17 .Rows(n).Hidden = (.Cells(n, 1) = "") Next n 'Spalten ausblenden For Each chkCell In .Range("B9:I9") If chkCell = "" Then chkCell.EntireColumn.Hidden = True For Each rngC In Intersect(.UsedRange, chkCell.EntireColumn.Cells) If Not rngC.Locked Then rngC.ClearContents Next rngC Else chkCell.EntireColumn.Hidden = False chkCell.EntireRow.Hidden = False End If Next chkCell End With Next i End Sub

Beliebteste Forumthreads (12 Monate)

8 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

8
Beiträge zum Forumthread

Beiträge zu diesem Forumthread