Code anpassen wenn Zelle A1 leer

Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range On Error GoTo ErrExit If Target.Address = "$A$1" Then With Application .EnableEvents = False .ScreenUpdating = False End With For Each rng In Range("C2:Z2") rng.EntireColumn.Hidden = rng Target Next End If ErrExit: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Application .EnableEvents = False .ScreenUpdating = False End With If Not Intersect(Target, Range("$A$1")) Is Nothing Then If Target = "" Then Columns("C:Z").EntireColumn.Hidden = False End If End If With Application .EnableEvents = True .ScreenUpdating = True End With End Sub

Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range On Error GoTo ErrExit If Target.Address = "$A$1" Then With Application .EnableEvents = False .ScreenUpdating = False End With For Each rng In Range("C2:Z2") rng.EntireColumn.Hidden = rng Target Next End If If Not Intersect(Target, Range("$A$1")) Is Nothing Then If Target = "" Then 'Wenn gefüllt Columns("C:Z").EntireColumn.Hidden = False End If End If ErrExit: With Application .EnableEvents = True .ScreenUpdating = True End With End Sub

Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range With Application .EnableEvents = False .ScreenUpdating = False End With If Not Intersect(Target, Range("$A$1")) Is Nothing Then For Each rng In Range("C2:Z2") If rng Target Then rng.EntireColumn.Hidden = True Else rng.EntireColumn.Hidden = False End If Next End If With Application .EnableEvents = True .ScreenUpdating = True End With End Sub

Hi Thomas,
die dich dazu angeregt haben es umzustellen.
Jetzt solltest nur noch dein $A$1 einmalig als Target definieren und erhältst das Endprodukt kurz und Schmerzlos:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error GoTo ErrExit
If Not Intersect(Target, Range("$A$1")) Is Nothing Then 'die reicht
Application.EnableEvents = False
Application.ScreenUpdating = False
For Each rng In Range("C2:Z2")
rng.EntireColumn.Hidden = rng  Target
Next
If Target = "" Then
Columns("C:Z").EntireColumn.Hidden = False
End If
End If
ErrExit:
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Schönen Restsonntag,
Matze

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

330 Forumthreads zu ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

Code anpassen wenn Zelle A1 leer

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

330 Forumthreads zu ähnlichen Themen

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread