AW: Makro ändern
29.03.2012 17:51:16
Uwe
Hallo Wolfgang,
ich habe mal zwei Zeilen in Steffens Code geändert und eine hinzugefügt, dann müsste es besser klappen. Teste aber nochmal genau:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myRange As Range, myFind, firstAdd
Dim ValidRange As Range
'Geändert:
'Set ValidRange = Cells(5, 8).Resize(Rows.Count - 5, 1)
Set ValidRange = Cells(5, 8).Resize(UsedRange.Rows.Count - 3, 1)
If Target.Address(0, 0) = "B2" Then
On Error GoTo errH
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
ValidRange.SpecialCells(xlCellTypeConstants).EntireRow.Hidden = False
Set myFind = ValidRange.Find(Target, lookat:=xlPart, LookIn:=xlValues, MatchCase:=False) _
_
If Not myFind Is Nothing Then
firstAdd = myFind.Address
Do
If myRange Is Nothing Then Set myRange = myFind
Set myRange = Union(myRange, myFind)
Set myFind = ValidRange.FindNext(myFind)
Loop Until firstAdd = myFind.Address
End If
End If
If Not myRange Is Nothing Then
'Geändert:
'ValidRange.SpecialCells(xlCellTypeConstants).EntireRow.Hidden = True
ValidRange.EntireRow.Hidden = True
myRange.EntireRow.Hidden = False
'Hinzugefügt:
Else: ValidRange.EntireRow.Hidden = False
End If
errH:
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
Gruß
Uwe
(:o)