VBA Code "tunen" / vereinfachen
Mike
folgende Frage an die Experten...: ;-)
Da ich mit VBA leider nicht allzu viel Erfahrung habe, hat mich der folgende Code doch einige Mühen gekostet, liefert aber im Ergebnis genau, was er soll... ;-)
Kann mir hier vielleicht noch jemand ein paar Tricks verraten, wie ich diesen laienhaften Code noch einig entschlacken und damit evtl. auch etwas schneller machen kann ?
Ich glaube, es sieht so weit mehr aus, als es ist... :-)
Sub Delete_Duplicates()
Sheets("Duplicates").Select
Cells.Select
Selection.ClearContents
Sheets("New").Select
Cells.Select
Selection.ClearContents
Sheets("All").Select
Columns("A:H").Select
Selection.ClearContents
Sheets("Import").Select
Columns("B:H").Select
Selection.Copy
Sheets("All").Select
Columns("B:H").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Import").Select
Columns("A:A").Select
Selection.Copy
Sheets("All").Select
Columns("A:A").Select
ActiveSheet.Paste
Sheets("Import").Select
Columns("D:D").Select
Selection.Copy
Sheets("All").Select
Columns("D:D").Select
ActiveSheet.Paste
Columns("L:L").Select
Application.CutCopyMode = False
Selection.Copy
Columns("A:A").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "d/m/yyyy"
Selection.ColumnWidth = 12.14
Columns("M:M").Select
Application.CutCopyMode = False
Selection.Copy
Columns("D:D").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Selection.NumberFormat = "d/m/yyyy"
Selection.ColumnWidth = 12.14
Columns("A:A").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("D:D").Select
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Sheets("All").Select
Cells.Select
Selection.Sort Key1:=Range("B1"), Order1:=xlAscending, Key2:=Range("F1") _
, Order2:=xlDescending, Header:=xlNo, OrderCustom:=1, MatchCase:=False _
, Orientation:=xlTopToBottom
Rows("1:1").Select
Selection.EntireRow.Insert
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=9, Criteria1:="="
Cells.Select
Selection.Copy
Sheets("New").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("All").Select
Selection.AutoFilter Field:=9, Criteria1:="duplicate"
Cells.Select
Selection.Copy
Sheets("Duplicates").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("All").Select
Selection.AutoFilter Field:=9
Selection.AutoFilter
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Import").Select
Range("A1").Select
Sheets("New").Select
Range("A1").Select
Sheets("Duplicates").Select
Range("A1").Select
Sheets("Report").Select
Range("A1").Select
End Sub
VG u. vielen Dank Euch für jeden Tipp,
Mike