Re: Spalten vergleichen von Oliver
26.08.2021 16:35:27
Oliver
Ich möchte mich beziehen auf die Antwort von Thorsten (Oberschlumpf) auf obiges Posting hier von Oliver:
https://www.herber.de/forum/messages/1845120.html
Erstmal vorneweg: Thorstens Code als Lösung funktioniert tadellos für das Problem vom Oliver:
Sub sbTest()
Dim lloRow As Long
With Sheets("Tabelle1") 'anpassen, wenn anders als "Tabelle1"
For lloRow = .Cells(.Rows.Count, 6).End(xlUp).Row To 1 Step -1
If LCase(Left(.Range("F" & lloRow).Value, 2)) = LCase(Sheets("Tabelle2").Range("Q4").Value) Then 'anpassen, wenn anders als "Tabelle2"
If LCase(.Range("G" & lloRow).Value) = "12a" Or _
LCase(.Range("G" & lloRow).Value) = "12b" Then
.Rows(lloRow).Delete
End If
End If
Next
End With
End Sub
- Berechnete Zellreferenzen oft mit Range und Buchstabe+Zahl zusammenbauen
- Als Blattreferenz immer den änderbaren Namen wie Sheets("Tabelle1") benutzen anstatt dem Blatt einen festen Namen zu geben
Der Umweg über Strings (wo eben nicht notwendig) kostet immens Rechenzeit! Insbesondere Konstrukte wie "Range("F" & lloRow)" legen einen ersten String "F" an, konvertieren eine Zahl "lloRow" in einen zweiten String und erzeugen durch Verbinden der beiden einen dritten!
Wenn wir hier von 100 Feldern reden, sicher völlig belanglos - aber auch nicht einfacher als mit dem Befehl "Cells()" zu arbeiten!?
Bei großen Mengen macht sich das aber deutlich in der Rechenzeit bemerkbar!
Deshalb meine Frage: Warum also? Gibt es Gründe, die sich mir nicht erschließen, das immer auf diese Weise zu machen?
Den einzigen Nachteil den ich bei "Cells(lloRow, 6)" vs. "Range("F" & lloRow)" erkenne ist, dass man wissen muss, das 6 für Spalte "F" steht. Bei höheren Zahlen braucht man deshalb oft alle Finger zum überprüfen :--)
Ich löse das für mich durch die Angabe der Spalte in einem Kommentar nach dem Befehl.
Ich habe jetzt mal eine Testmappe gebaut
https://www.herber.de/bbs/user/147803.xlsm
in der ich Varianten von Thorstens Code auf 100.000 Datensätze (zwei Werte in Spalten F und G) losgelassen habe.
Die Datensätze musste ich leider vor dem Hochladen hier löschen, sonst hätte ich die hier zulässige Größe von 300 kByte überschritten.
Deshalb bitte nach dem Start in Tabelle 2 mit Button "Tabelle1 neu füllen" neue Zufallsdaten erzeugen.
Hier die von mit verwendeten Codeteile.
Original von Thorsten an die Mappe angepasst:
[Ich habe den Befehl ".Rows(lloRow).Delete" durch " .Cells(lloRow, 8) = "X"" ersetzt, weil ja sonst eine Wiederholung des Tests nicht möglich wäre.]
Sub sbTest(Zeit As Long)
'Orginialsyntax
Dim lloRow As Long
Call ClearH
lngTime = GetTickCount '====================
With Sheets("Tabelle1")
For lloRow = .Cells(.Rows.Count, 6).End(xlUp).Row To 1 Step -1 'Spalte F
If LCase(Left(.Range("F" & lloRow).Value, 2)) = LCase(Sheets("Tabelle2").Range("C3").Value) Then
If LCase(.Range("G" & lloRow).Value) = "12a" Or LCase(.Range("G" & lloRow).Value) = "12b" Then
.Cells(lloRow, 8) = "X" 'Spalte H Original: .Rows(lloRow).Delete
End If
End If
Next
End With
Zeit = GetTickCount - lngTime '==================== Testzeit in ms
End Sub
Sub sbTestF(Zeit As Long)
'Final Syntax
Dim lloRow As Long, A$
Call ClearH
lngTime = GetTickCount '====================
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
A$ = shtAus.Cells(3, 3)
With shtTab1
For lloRow = .Cells(Rows.Count, 6).End(xlUp).Row To 1 Step -1 'Spalte F
If StrComp(Left(.Cells(lloRow, 6), 2), A$, vbTextCompare) = 0 Then
Select Case LCase(.Cells(lloRow, 7)) 'Spalte G
Case "12a", "12b"
.Cells(lloRow, 8) = "X" 'Spalte H
End Select
End If
Next
End With
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
Zeit = GetTickCount - lngTime '==================== Testzeit in ms
End Sub
Da das Betriebssystem Excel nicht immer die gleichen Rechenzeitscheiben zur Verfügung stellt, mache ich immer 5 Durchläufe und ein Mittel in Millisekunden.
Neben den beiden habe ich noch zwei Fälle getestet und dadurch sieht man deutlich, dass die Hauptersparnis durch Weglassen der Stringberechnungen und dem unnötigen mehrfachen Zugriff auf ein und dieselbe Zelle im Blatt gemacht wird.
Ihr könnt ja im Code auch andere Varianten testen.
Ich dachte, ich teile diese Erkenntnisse mal mit Euch.