AW: Spalten sortieren
06.05.2015 14:51:21
UweD
Hallo
so ?
Sub sort()
Dim no As Integer
Dim letzteZT1 As Integer
With ActiveWorkbook.Sheets("Tabelle2")
Range("I:J").ClearContents
letzteZT1 = .Range("A" & Rows.Count).End(xlUp).Row
.Range("A1:B" & letzteZT1).Copy Range("I1")
letzteZT1 = .Range("I" & Rows.Count).End(xlUp).Row
.Range("E1:F" & letzteZT1).Copy .Range("I" & letzteZT1 + 1)
'sortieren
.sort.SortFields.Clear
.sort.SortFields.Add Key:=Range("I:I"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal
.sort.SetRange Range("I:J")
.sort.Header = xlNo
.sort.MatchCase = False
.sort.Orientation = xlTopToBottom
.sort.SortMethod = xlPinYin
.sort.Apply
letzteZT1 = .Range("I" & Rows.Count).End(xlUp).Row
For no = letzteZT1 To 2 Step -1
If .Cells(no, 9) - .Cells(no - 1, 9) > 10 Then
Range(Cells(no, 9), Cells(no, 10)).Insert xlDown
.Cells(no, 9) = (Int(.Cells(no + 1, 9) / 10) - 1) * 10
.Cells(no, 10) = .Cells(no - 1, 10)
no = no + 1
ElseIf .Cells(no, 9) = .Cells(no - 1, 9) Then 'doppelt enthalten
Range(Cells(no, 9), Cells(no, 10)).Delete xlUp
End If
Next no
End With
End Sub
Gruß UweD