AW: Performance Zeilen löschen
16.08.2010 16:53:02
Tino
Hallo,
kannst mal diesen Code testen.
Die Uhrzeit, Spalte und die Tabelle müsstest Du im Code noch anpassen.
Sub Loeschen_Mit_Formel()
Dim oSH As Worksheet, iCalc As Integer
Dim timefrom As Double
Dim strFormel$, strSpalte$
'Spalte mit Uhrzeiten
strSpalte = 1
'die Uhrzeit anpassen
timefrom = TimeSerial(17, 0, 0)
'Tabelle anpassen
Set oSH = Sheets("Tabelle1")
With Application
iCalc = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
With oSH.UsedRange
With .Columns(.Columns.Count).Offset(0, 1)
'entsprechende Formel
strFormel$ = _
"=IF(AND(ROW()>1,TIME(HOUR(RC1),MINUTE(RC1),SECOND(RC1))<" & _
Replace(CStr(timefrom), ",", ".") & "),True,ROW())"
strFormel$ = Replace(strFormel, "RC1", "RC" & strSpalte)
.Formula = strFormel$
oSH.UsedRange.Sort Key1:=.Cells(1, 1), Order1:=xlAscending, Header:=xlNo
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, 4).EntireRow.Delete
.EntireColumn.Delete
On Error GoTo 0
End With
End With
.ScreenUpdating = True
.Calculation = iCalc
End With
End Sub
Gruß Tino