HERBERS Excel-Forum - das Archiv
select vermeiden
Peter

Hallo Zusammen,
ich versuche mein Code zu beschleunigen. Ich habe gelesen, dass man den Select-Befehl vermeiden sollte. Wie könnte mann den folgenden Teil meines Codes verändern damit er schneller läuft?
.
.
.
Range("B23:I1000").Select
Selection.Delete Shift:=xlUp
'---------------------------------------------------------------------------------
Sheets("Umsatz_Absatz_alle").Select
Rows("8:8").Select
Selection.AutoFilter
.lz = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row 'letzte Zeile bestimmen
.ls = Cells(6, Columns.Count).End(xlToLeft).Column 'letzte Spalte bestimmen
x = 0
For y = 7 To lz
If Cells(y, 2) = "" And Cells(y, 3) = "" Then
Exit For
Else
If Cells(y, 2) = Kundennummer And Cells(y, 5) >= Datumstart And Cells(y, 5) <= datumende Then
arrp(1) = Cells(y, 5) 'Datum
arrp(2) = Cells(y, 8) 'Produkt
arrp(3) = Cells(y, 12) 'Menge
arrp(4) = Cells(y, 13) 'Umsatz
lzl = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Cells(lzl + 1, 2) = arrp(1) 'Datum
Cells(lzl + 1, 3) = arrp(2) 'Produkt
Cells(lzl + 1, 4) = arrp(3) 'Menge
Cells(lzl + 1, 5) = arrp(4) 'Umsatz
On Error GoTo weiter
Cells(lzl + 1, 6) = arrp(4) / arrp(3) 'Durchschnittspreis
weiter:
End With
End If
End If
Sheets("Umsatz_Absatz_alle").Select
Next y
.
.
.
Vielen Dank für Eure Antworten,
LG, Peter

AW: select vermeiden
Daniel

Hi
naja, soviele zeitkritische Selects hast du da nicht drin.
das einzige wäre das hier:

Range("B23:I1000").Select
Selection.Delete Shift:=xlUp

mit 
Range("B23:I1000").Delete Shift:=xlUp
und das beim Autofilter genauso aber irgendwie ist es Unsinn, den Autofilter auf eine einzige Zeile anzuwenden. Da einfach nochmal genauer drüber nachdenken, was du überhaupt vor hast.
ansonsten kannst du noch die Anzahl der Zellzugriffe reduzieren, indem du das Array arrp als ganzes in die Tabelle zurückschreibst und vorher noch den Durchschnittspreis als 5. Elementn mit aufnimmst:
arrp(5) = arrp(4) / arrp(3)
cells(lzl + 1, 2).resize(1,  ubound(arrp) - lbound(arrp) - 1).value = arrp
weitere Optimierungsmöglichkeiten wären, dass du die Liste nach Kundennummer und Datum sortiest, dann muss die Schleife nicht über alle Zeilen laufen lassen, sondern nur über die mit der passenden Kundennummer.
Gruß Daniel

AW: select vermeiden
Peter

Hallo Daniel,
danke für deine Antwort. Diese Zeile verstehe ich nicht.
cells(lzl + 1, 2).resize(1, ubound(arrp) - lbound(arrp) - 1).value = arrp
Wird diese Zeile außerhalb der Schleife eingefügt?
Sheets("Kundenoverview").Select
KundenName = Cells(6, 3)
Kundennummer = Cells(6, 5)
Datumstart = Cells(8, 4)
datumende = Cells(8, 6)
zz = 23 'Tabellenanfang
Application.ScreenUpdating = False ' NACH DEM TEST aktivieren
'---------------------------------------------------------------------------------
'Am anfang wird die Tabelle gelöscht
'---------------------------------------------------------------------------------
Range("B23:I1000").Delete Shift:=xlUp
'---------------------------------------------------------------------------------
Sheets("Umsatz_Absatz_alle").Select
Rows("8:8").Select
Selection.AutoFilter
.lz = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row 'letzte Zeile bestimmen
.ls = Cells(6, Columns.Count).End(xlToLeft).Column 'letzte Spalte bestimmen
x = 0
For y = 7 To lz
If Cells(y, 2) = "" And Cells(y, 3) = "" Then
Exit For
Else
If Cells(y, 2) = Kundennummer And Cells(y, 5) >= Datumstart And Cells(y, 5) <= datumende Then
arrp(1) = Cells(y, 5) 'Datum
arrp(2) = Cells(y, 8) 'Produkt
arrp(3) = Cells(y, 12) 'Menge
arrp(4) = Cells(y, 13) 'Umsatz
On Error GoTo weiter
arrp(5) = arrp(4) / arrp(3) 'Durchschnittspreis
weiter:
lzl = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Cells(lzl + 1, 2) = arrp(1) 'Datum
Cells(lzl + 1, 3) = arrp(2) 'Produkt
Cells(lzl + 1, 4) = arrp(3) 'Menge
Cells(lzl + 1, 5) = arrp(4) 'Umsatz
End If
End If
Sheets("Umsatz_Absatz_alle").Select
Next y
LG,
Peter

AW: select vermeiden
Peter

Hallo Daniel,
könntest du bitte deine Zeile sehr kurz erklären.
cells(lzl + 1, 2).resize(1, ubound(arrp) - lbound(arrp) - 1).value = arrp
Danke!
LG,
Peter

AW: select vermeiden
Daniel

Hi
das schreibt das ganze Array auf einen Schlag in das Tabellenblatt zurück.
hierbei muss beachtet werden, dass der angegebene Zellbereich in Zeilen und Spalten genauso viele Elemente hat wie das Array
da du ein eindimensionales Array hast, ist das für diese Aktion wie ein Zweidimensionales Array mit einer Zeile und vielen Spalten.
Ubound ist der grösste Index im Array, Lbound ist der kleinste Index.
über Ubound - Lbound + 1 bekommt man die Anzahl der Elemente.
der Lbound ist, je nachdem wie das Array erstellt wurde, 0 oder 1, er kann aber theoretisch jeden anderen Wert annehmen.
Wenn man (so wie ich bei deinem Codeausschnitt) nicht genau weiss, wie das Array erstellt wurde, muss man die Anzahl der Elemente auf diese weise berechnen.
der Vorteil dieser Vorgehensweise ist, dass Excel jedesmal, wenn du einen Wert in eine Zelle schreibst, ein paar Dinge zu tun hat, bspw prüfen, ob Formeln von dieser Änderung betroffen sind und neu berechnet werden müssen.
Wenn du jetzt jeden Wert einzeln in die Zellen schreibst, laufen diese Aktionen auch 5x ab.
Wenn du das Array als ganzes auf einen Schlag in die Zellen schreibst, laufen diese Aktionen nur 1x ab, weil Excel das für alle Zellen gleichzeitig mit prüfen kann.
Funktioniert natürlich nur, wenn das Array lückenlos in einen Zellbereich geschrieben werden soll.
Gruß Daniel

AW: select vermeiden
Peter

Hallo Daniel,
sorry ich habe vorhin in meinem Code eine Zeile ( zuviel gelöscht. Anbei nochmal der Code.
Das Problem was ich habe ist, daß in meiner Schleife ganze Zeit zwischen zwei Tabellen hin uund her gesprungen wird, was sicherlich geschickter gelöst werden kann.
Sheets("Kundenoverview").Select
KundenName = Cells(6, 3)
Kundennummer = Cells(6, 5)
Datumstart = Cells(8, 4)
datumende = Cells(8, 6)
zz = 23 'Tabellenanfang
Application.ScreenUpdating = False ' NACH DEM TEST aktivieren
'---------------------------------------------------------------------------------
'Sprung zu Tabelle: gelieferte Produkte
'---------------------------------------------------------------------------------
'---------------------------------------------------------------------------------
'Berechnung der Anzahl der Produkte gemäß vorgabe aus Kunden-Overview
'---------------------------------------------------------------------------------
'---------------------------------------------------------------------------------
'Am anfang wird die Tabelle gelöscht
'---------------------------------------------------------------------------------
Range("B23:I1000").Delete Shift:=xlUp
'---------------------------------------------------------------------------------
Sheets("Umsatz_Absatz_alle").Select
Rows("8:8").Select
Selection.AutoFilter
.lz = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row 'letzte Zeile bestimmen
.ls = Cells(6, Columns.Count).End(xlToLeft).Column 'letzte Spalte bestimmen
x = 0
For y = 7 To lz
If Cells(y, 2) = "" And Cells(y, 3) = "" Then
Exit For
Else
If Cells(y, 2) = Kundennummer And Cells(y, 5) >= Datumstart And Cells(y, 5) <= datumende Then
arrp(1) = Cells(y, 5) 'Datum
arrp(2) = Cells(y, 8) 'Produkt
arrp(3) = Cells(y, 12) 'Menge
arrp(4) = Cells(y, 13) 'Umsatz
On Error GoTo weiter
arrp(5) = arrp(4) / arrp(3) 'Durchschnittspreis
weiter:
Sheets("Kundenoverview").Select
lzl = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Cells(lzl + 1, 2) = arrp(1) 'Datum
Cells(lzl + 1, 3) = arrp(2) 'Produkt
Cells(lzl + 1, 4) = arrp(3) 'Menge
Cells(lzl + 1, 5) = arrp(4) 'Umsatz
End If
End If
Sheets("Umsatz_Absatz_alle").Select
Next y

AW: select vermeiden
Daniel

HI
das mal durcharbeiten:
http://www.online-excel.de/excel/singsel_vba.php?f=78
Gruß Daniel

AW: select vermeiden
matthias

Hallo Peter,
wenn es nur ums Beschleunigen gehen soll, helfen diese zwei Befehle sehr oft:
Application.ScreenUpdating
Application.Calculation
Der erste steuert die Bildschirmakualisierung, der zweite die Neuberechnung der Formeln. Logisch dass beides Ressourcen frisst und während der Laufzeit deiner Makros im Normalfall ausgeschaltet werden kann:
Application.ScreenUpdating = False
Application.Calculation = xlManual
Vergiss nicht am Ende des Makros den Ursprungszustand wieder herzustellen. Die Neuberechnung von Formeln hat schon Leute in den Wahnsinn getrieben (mich eingeschlossen), weil sie sich gewundert haben warum ihre Formeln nicht funktionieren:
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
lg Matthias

select vermeiden
Peter

Hallo Zusammen,
ich versuche mein Code zu beschleunigen. Ich habe gelesen, dass man den Select-Befehl vermeiden sollte. Wie könnte mann den folgenden Teil meines Codes verändern damit er schneller läuft?
.
.
.
Range("B23:I1000").Select
Selection.Delete Shift:=xlUp
'---------------------------------------------------------------------------------
Sheets("Umsatz_Absatz_alle").Select
Rows("8:8").Select
Selection.AutoFilter
.lz = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row 'letzte Zeile bestimmen
.ls = Cells(6, Columns.Count).End(xlToLeft).Column 'letzte Spalte bestimmen
x = 0
For y = 7 To lz
If Cells(y, 2) = "" And Cells(y, 3) = "" Then
Exit For
Else
If Cells(y, 2) = Kundennummer And Cells(y, 5) >= Datumstart And Cells(y, 5) <= datumende Then
arrp(1) = Cells(y, 5) 'Datum
arrp(2) = Cells(y, 8) 'Produkt
arrp(3) = Cells(y, 12) 'Menge
arrp(4) = Cells(y, 13) 'Umsatz
lzl = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Cells(lzl + 1, 2) = arrp(1) 'Datum
Cells(lzl + 1, 3) = arrp(2) 'Produkt
Cells(lzl + 1, 4) = arrp(3) 'Menge
Cells(lzl + 1, 5) = arrp(4) 'Umsatz
On Error GoTo weiter
Cells(lzl + 1, 6) = arrp(4) / arrp(3) 'Durchschnittspreis
weiter:
End With
End If
End If
Sheets("Umsatz_Absatz_alle").Select
Next y
.
.
.
Vielen Dank für Eure Antworten,
LG, Peter

AW: select vermeiden
Daniel

Hi
naja, soviele zeitkritische Selects hast du da nicht drin.
das einzige wäre das hier:

Range("B23:I1000").Select
Selection.Delete Shift:=xlUp

mit 
Range("B23:I1000").Delete Shift:=xlUp
und das beim Autofilter genauso aber irgendwie ist es Unsinn, den Autofilter auf eine einzige Zeile anzuwenden. Da einfach nochmal genauer drüber nachdenken, was du überhaupt vor hast.
ansonsten kannst du noch die Anzahl der Zellzugriffe reduzieren, indem du das Array arrp als ganzes in die Tabelle zurückschreibst und vorher noch den Durchschnittspreis als 5. Elementn mit aufnimmst:
arrp(5) = arrp(4) / arrp(3)
cells(lzl + 1, 2).resize(1,  ubound(arrp) - lbound(arrp) - 1).value = arrp
weitere Optimierungsmöglichkeiten wären, dass du die Liste nach Kundennummer und Datum sortiest, dann muss die Schleife nicht über alle Zeilen laufen lassen, sondern nur über die mit der passenden Kundennummer.
Gruß Daniel

AW: select vermeiden
Peter

Hallo Daniel,
danke für deine Antwort. Diese Zeile verstehe ich nicht.
cells(lzl + 1, 2).resize(1, ubound(arrp) - lbound(arrp) - 1).value = arrp
Wird diese Zeile außerhalb der Schleife eingefügt?
Sheets("Kundenoverview").Select
KundenName = Cells(6, 3)
Kundennummer = Cells(6, 5)
Datumstart = Cells(8, 4)
datumende = Cells(8, 6)
zz = 23 'Tabellenanfang
Application.ScreenUpdating = False ' NACH DEM TEST aktivieren
'---------------------------------------------------------------------------------
'Am anfang wird die Tabelle gelöscht
'---------------------------------------------------------------------------------
Range("B23:I1000").Delete Shift:=xlUp
'---------------------------------------------------------------------------------
Sheets("Umsatz_Absatz_alle").Select
Rows("8:8").Select
Selection.AutoFilter
.lz = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row 'letzte Zeile bestimmen
.ls = Cells(6, Columns.Count).End(xlToLeft).Column 'letzte Spalte bestimmen
x = 0
For y = 7 To lz
If Cells(y, 2) = "" And Cells(y, 3) = "" Then
Exit For
Else
If Cells(y, 2) = Kundennummer And Cells(y, 5) >= Datumstart And Cells(y, 5) <= datumende Then
arrp(1) = Cells(y, 5) 'Datum
arrp(2) = Cells(y, 8) 'Produkt
arrp(3) = Cells(y, 12) 'Menge
arrp(4) = Cells(y, 13) 'Umsatz
On Error GoTo weiter
arrp(5) = arrp(4) / arrp(3) 'Durchschnittspreis
weiter:
lzl = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Cells(lzl + 1, 2) = arrp(1) 'Datum
Cells(lzl + 1, 3) = arrp(2) 'Produkt
Cells(lzl + 1, 4) = arrp(3) 'Menge
Cells(lzl + 1, 5) = arrp(4) 'Umsatz
End If
End If
Sheets("Umsatz_Absatz_alle").Select
Next y
LG,
Peter

AW: select vermeiden
Peter

Hallo Daniel,
könntest du bitte deine Zeile sehr kurz erklären.
cells(lzl + 1, 2).resize(1, ubound(arrp) - lbound(arrp) - 1).value = arrp
Danke!
LG,
Peter

AW: select vermeiden
Daniel

Hi
das schreibt das ganze Array auf einen Schlag in das Tabellenblatt zurück.
hierbei muss beachtet werden, dass der angegebene Zellbereich in Zeilen und Spalten genauso viele Elemente hat wie das Array
da du ein eindimensionales Array hast, ist das für diese Aktion wie ein Zweidimensionales Array mit einer Zeile und vielen Spalten.
Ubound ist der grösste Index im Array, Lbound ist der kleinste Index.
über Ubound - Lbound + 1 bekommt man die Anzahl der Elemente.
der Lbound ist, je nachdem wie das Array erstellt wurde, 0 oder 1, er kann aber theoretisch jeden anderen Wert annehmen.
Wenn man (so wie ich bei deinem Codeausschnitt) nicht genau weiss, wie das Array erstellt wurde, muss man die Anzahl der Elemente auf diese weise berechnen.
der Vorteil dieser Vorgehensweise ist, dass Excel jedesmal, wenn du einen Wert in eine Zelle schreibst, ein paar Dinge zu tun hat, bspw prüfen, ob Formeln von dieser Änderung betroffen sind und neu berechnet werden müssen.
Wenn du jetzt jeden Wert einzeln in die Zellen schreibst, laufen diese Aktionen auch 5x ab.
Wenn du das Array als ganzes auf einen Schlag in die Zellen schreibst, laufen diese Aktionen nur 1x ab, weil Excel das für alle Zellen gleichzeitig mit prüfen kann.
Funktioniert natürlich nur, wenn das Array lückenlos in einen Zellbereich geschrieben werden soll.
Gruß Daniel

AW: select vermeiden
Peter

Hallo Daniel,
sorry ich habe vorhin in meinem Code eine Zeile ( zuviel gelöscht. Anbei nochmal der Code.
Das Problem was ich habe ist, daß in meiner Schleife ganze Zeit zwischen zwei Tabellen hin uund her gesprungen wird, was sicherlich geschickter gelöst werden kann.
Sheets("Kundenoverview").Select
KundenName = Cells(6, 3)
Kundennummer = Cells(6, 5)
Datumstart = Cells(8, 4)
datumende = Cells(8, 6)
zz = 23 'Tabellenanfang
Application.ScreenUpdating = False ' NACH DEM TEST aktivieren
'---------------------------------------------------------------------------------
'Sprung zu Tabelle: gelieferte Produkte
'---------------------------------------------------------------------------------
'---------------------------------------------------------------------------------
'Berechnung der Anzahl der Produkte gemäß vorgabe aus Kunden-Overview
'---------------------------------------------------------------------------------
'---------------------------------------------------------------------------------
'Am anfang wird die Tabelle gelöscht
'---------------------------------------------------------------------------------
Range("B23:I1000").Delete Shift:=xlUp
'---------------------------------------------------------------------------------
Sheets("Umsatz_Absatz_alle").Select
Rows("8:8").Select
Selection.AutoFilter
.lz = ActiveSheet.Cells(Rows.Count, 3).End(xlUp).Row 'letzte Zeile bestimmen
.ls = Cells(6, Columns.Count).End(xlToLeft).Column 'letzte Spalte bestimmen
x = 0
For y = 7 To lz
If Cells(y, 2) = "" And Cells(y, 3) = "" Then
Exit For
Else
If Cells(y, 2) = Kundennummer And Cells(y, 5) >= Datumstart And Cells(y, 5) <= datumende Then
arrp(1) = Cells(y, 5) 'Datum
arrp(2) = Cells(y, 8) 'Produkt
arrp(3) = Cells(y, 12) 'Menge
arrp(4) = Cells(y, 13) 'Umsatz
On Error GoTo weiter
arrp(5) = arrp(4) / arrp(3) 'Durchschnittspreis
weiter:
Sheets("Kundenoverview").Select
lzl = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Cells(lzl + 1, 2) = arrp(1) 'Datum
Cells(lzl + 1, 3) = arrp(2) 'Produkt
Cells(lzl + 1, 4) = arrp(3) 'Menge
Cells(lzl + 1, 5) = arrp(4) 'Umsatz
End If
End If
Sheets("Umsatz_Absatz_alle").Select
Next y

AW: select vermeiden
Daniel

HI
das mal durcharbeiten:
http://www.online-excel.de/excel/singsel_vba.php?f=78
Gruß Daniel

AW: select vermeiden
matthias

Hallo Peter,
wenn es nur ums Beschleunigen gehen soll, helfen diese zwei Befehle sehr oft:
Application.ScreenUpdating
Application.Calculation
Der erste steuert die Bildschirmakualisierung, der zweite die Neuberechnung der Formeln. Logisch dass beides Ressourcen frisst und während der Laufzeit deiner Makros im Normalfall ausgeschaltet werden kann:
Application.ScreenUpdating = False
Application.Calculation = xlManual
Vergiss nicht am Ende des Makros den Ursprungszustand wieder herzustellen. Die Neuberechnung von Formeln hat schon Leute in den Wahnsinn getrieben (mich eingeschlossen), weil sie sich gewundert haben warum ihre Formeln nicht funktionieren:
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
lg Matthias

Bewerten Sie hier bitte das Excel-Portal