Zeile löschen bei großen, dyn. Tabellen langsam!
24.01.2014 14:11:01
Steve
meine u.g. Routine übernimmt von einem Eingabefeld die Zahl (Long) und löscht sämtliche Zeilen (einer dynamischen Tabelle), die in der 7. Spalte (G) den eingegebenen Wert hat.
Klappt soweit wunderbar. Jedoch dauert die Abfrage bei großen Tabellen. Hat jemand einen Verbesserungsvorschlag?
Option Explicit
Public Sub sel_PMT()
Dim sel_PMT As Variant
Dim i As Long
Dim last_row As Long
Do
sel_PMT = Application.InputBox(prompt:="Please enter PMT:", Title:="Enter PMT", Default:=1, _
_
Type:=1)
If VarType(sel_PMT) = vbBoolean Then Exit Do
If sel_PMT = 1 And sel_PMT 6 Then
'Dim myRange As Range
Sheets("twDataSheet").Activate
Application.ScreenUpdating = False
last_row = getlastrow("twDataSheet", 7, 3) '7 = Column number; 4 = Start row
For i = last_row To 3 Step -1 '4 = Startreihe
If Cells(i, 7) sel_PMT Then Rows(i).Delete '7 = Column number;
Next i
Application.ScreenUpdating = True
Sheets("Settings").Activate
MsgBox "All Reports have now been filtered for PMT " & sel_PMT, vbOKOnly, "Information"
Exit Do
End If
MsgBox "ERROR! Only numbers between 1 to 7 (without 6)!", 16, "Warning!"
Loop
End Sub
Public Function getlastrow(sh As String, col As Long, startrow As Long) As Long
Dim i As Long
For i = startrow To 150000
If Sheets(sh).Cells(i, col) = "" And Sheets(sh).Cells(i + 1, col) = "" Then '
getlastrow = i
Exit Function
End If
Next i
End Function