AW: Dynamische Tabelle automatisch sortieren
03.08.2018 15:57:12
Nepumuk
Hallo Gerhard,
teste mal:
Option Explicit
Option Private Module
Public Sub sbSort()
Application.EnableEvents = False
With ThisWorkbook.Worksheets("Personen")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=.Range("A1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.Sort.SortFields.Add Key:=.Range("B1"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
With .Sort
.SetRange ThisWorkbook.Worksheets("Personen").Columns("A:E")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
Application.EnableEvents = True
End Sub
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
If Not IsEmpty(Target.Value) And Not _
IsEmpty(Target.Offset(0, 1).Value) Then Call sbSort
ElseIf Target.Column = 2 Then
If Not IsEmpty(Target.Value) And Not _
IsEmpty(Target.Offset(0, -1).Value) Then Call sbSort
End If
End Sub
Gruß
Nepumuk