VBA Code schneller machen

Ac3 · 26. Oktober 2017

Hallo Ihr Lieben,

ich habe eine (für mich leider doch nicht so) einfache Frage ansich.
Ich habe diesen Code geschrieben. Der auch das tut was ich möchte. Ich habe in der Spalte G viele Werte aber auch viele leere Zellen. Mein Code löscht "einfach alle leeren Zellen in dieser Spalte G. Nur ist dieser Code unendlich langsam und ich weiß nicht wie ich das schneller machen könnte. Eventuell kann mir ja jemand einen Tip geben bitte

Sub test()

For a = 2 To Worksheets("Tabelle1").UsedRange.Rows.Count
If Cells(a, 7).Value = "" Then
Cells(a, 7).Delete
a = a - 1
End If
If Cells(a, 7).Value <> "" Then
End If

Next a

End Sub

Liebe Grüße
Ac3

Wehrwolf · 26. Oktober 2017

Vielleicht bringt es was alle Zellen die gelöscht werden sollen erst mal zu speichern und sie am Ende auf einmal zu löschen?

Hanfyy · 26. Oktober 2017

Als erstes kannst du den
If Cells(a, 7).Value <> "" Then
End If
teil weglassen -> entweder leer oder nicht leer, man muss nicht beides prüfen.

Sub test()

For a = 2 To Worksheets("Tabelle1").UsedRange.Rows.Count
If Cells(a, 7).Value = "" Then
Cells(a, 7).Delete
a = a - 1
End If

Next a

End Sub

Mach nen begrenzer rein, ermittle die letzte Zeile, in der was geschrieben steht und mach einen Exit Sub wenn a bei dieser Zeile ist.
Das sind so die einfachsten Möglichkeiten um etwas Speed raus zu kitzeln.

//hier stand ein Code mit Fehler//

chris221177 · 26. Oktober 2017

Hallo Ac3,

ich bin zwar überhaupt kein VBA Experte, aber wenigstens im Bereich Programmierung tätig. Wenn ich mir den Code anschaue, fallen mir direkt 2 Dinge auf:

1. in der Iteration über die Zeilen, ist es aus Performance-Sicht sicher schlecht jedes Mal über die UsedRange.Rows.Count zu gehen. Die würde ich vor der for-Schleife zuweisen und innerhalb gegebenenfalls dekrementieren.
2. *If Cells(a, 7).Value <> "" Then* Mache nix, würde ich falls da nichts mehr rein muss, eliminieren.
3. Falls beides nicht viel bringt, würde ich mal versuchen, Zeitmessungen zu machen, indem mal mit Delete und ohne Delete gearbeitet wird. So kann man den Zeitfresser ausfindig machen. Eventuell gibt es dann alternative Funktionalitäten, die performanter sind. Da muss dann aber jemand helfen, der sich mit VBA und Excel besser ausgennt.

Ich kenne die Performance - Problematik aus PHP-Excel. Da war zumindest die Suche, nach dem genutzten Bereich richtig schlecht. Das summiert sich ja dann übelst auf durch die Iteration, weil Excel vermutlich keine gecachten Werte benutzt, sondern die schlechte interne Implementierung wieder und wieder ansteuert...

Ich hoffe, das hilft...

Viele Grüße
Chris

mac4life · 26. Oktober 2017

Da gibt einiges an Potenzial.

Möglichkeiten
- Code optimieren
- automatische Berechnung, Displayaktualisierung, etc. ausschalten
- Alles durchlaufen, nicht leere in ein Array schreiben und dann das volle Array komplett ausgeben

Bin unterwegs, kann dir aber gleich mal ein paar Beispiele Posten.

Turner123 · 26. Oktober 2017

Leider kann ich es gerade nicht ausprobieren, aber ich vermute, dass das Problem in der Zeile
For a = 2 To Worksheets("Tabelle1").UsedRange.Rows.Count liegt. Bei jedem Schleifendurchlauf wird UsedRange.Rows.Count neu ausgewertet.

Probiere mal folgendes:
Sub test()

AnzahlZeilen = Worksheets("Tabelle1").UsedRange.Rows.Count
a = 2
For b = 2 To AnzahlZeilen
If Cells(a, 7).Value = "" Then
Cells(a, 7).Delete
a = a - 1
End If
If Cells(a, 7).Value <> "" Then
a = a + 1
End If

Next a

End Sub

Hanfyy · 26. Oktober 2017

Oder so:

Sub test2()

ActiveSheet.Range("G1:G1048576").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

End Sub

Schneller geht glaub nicht :'D

Ac3 · 26. Oktober 2017

Hallo @all

erstmals Danke für all eure Antworten und Hilfen

@Werwolf: Leider verstehe ich deinen Tip nicht ganz

Tut mir leid
@Hanfyy: Danke für diesen Code. Er ist auf jeden Fall schon schneller als meiner. Leider immernoch relativ langsam sodass ich bestimmt 3 Minuten warten muss, bis alle Zellen überprüft wurden (das sind ca. 1500) Stück.
@Chris221177: Danke für die Hinweise. Habe sie zusammen mit Hanfyys Tips umgesetzt. Ergebnis ist auf jeden fall besser aber immer noch nicht so wie ich es gerne hätte leider. Ich weiß auch nicht genau ob es einfach nicht schneller geht das Ganze.
@mac4life: Das mit dem vollen Array klingt interessant. Wie würde ich das denn umsetzen? Werde mich direkt mal umschauen ob ich selber was finde aber evtl kannst du mich ja etwas unterstützen

@Turner123: Danke auch für deine Hilfe, aber dieser Code hat leider nicht funktioniert. Der hat da irgendwie Zellen übersprungen Oo

Nochmals danke an alle von euch

G-Red · 26. Oktober 2017

Ac3 schrieb:
Hallo Ihr Lieben,

ich habe eine (für mich leider doch nicht so) einfache Frage ansich.
Ich habe diesen Code geschrieben. Der auch das tut was ich möchte. Ich habe in der Spalte G viele Werte aber auch viele leere Zellen. Mein Code löscht "einfach alle leeren Zellen in dieser Spalte G. Nur ist dieser Code unendlich langsam und ich weiß nicht wie ich das schneller machen könnte. Eventuell kann mir ja jemand einen Tip geben bitte

Sub test()

For a = 2 To Worksheets("Tabelle1").UsedRange.Rows.Count
If Cells(a, 7).Value = "" Then
Cells(a, 7).Delete
a = a - 1
End If
If Cells(a, 7).Value <> "" Then
End If

Next a

End Sub

Liebe Grüße
Ac3

Hi,

wozu so umständlich?
Damit lässt sich deine Aufgabe auch wunderbar umsetzen.

Sub DeleteEmptyRow()
Dim rngCount As Integer

rngCount = Worksheets("Tabelle1").UsedRange.Rows.Count

Range("G2:G" & rngCount).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.EntireRow.Delete
End Sub

Habe den Code mit Makrorecorder aus dieser Anleitung erstellt und etwas angepasst.
http://www.traens.com/tipps/microsoft/excel-leere-zeilen-loeschen.html

Turner123 · 26. Oktober 2017

Ich habe gerade den Fehler in meinem Code gesehen. Ich habe beim next a und b vertauscht. Korrekt ist also next b .

Hanfyy · 26. Oktober 2017

WEnn du grob die Zeilenanzahl kennst kannst du diese anpassen:

Sub test2()

ActiveSheet.Range("G1:G1048576").SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp

End Sub

zB 3000 rein schreiben beschleunigt die Sache ggf. nochmal.
oder Wieder die letzte Zeile suchen und als Range("G1:G" & letztezeile)

€:
Ich sehe gerade G-Red hat im Endeffekt genau das in seinem Code ergänzt. er nennt es nur anders.

Allerdings würde ich die .select und anschließend selection. ... nicht machen.

Bei Seinem Code kann man das auch zusammenführen:
Range("G2:G" & rngCount).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Ac3 · 26. Oktober 2017

@Turner123: Jetzt hat es geklappt aber auch noch recht langsam.

@Hanfyy/G-Red: DAS ist das, was ich gesucht habe. geht ratzfatz.
Nur hat sich ein weiteres Problem gezeigt. Es ist so, dass ich in meinen Zellen der Spalte G, Formeln drinstehen habe und Excel das wohl mit beachtet und diese Zellen auch nicht löscht

selbst wenn kein Wert angezeigt wird, steht ja quasi eine Formel drinne :/
(Es sind Wenn-Formeln die einen Wert nur unter bestimmten Voraussetzungen anzeigen.)
Gibt es dazu auch eine Lösung?

G-Red · 26. Oktober 2017

Ac3 schrieb:
@Turner123: Jetzt hat es geklappt aber auch noch recht langsam.

@Hanfyy/G-Red: DAS ist das, was ich gesucht habe. geht ratzfatz.
Nur hat sich ein weiteres Problem gezeigt. Es ist so, dass ich in meinen Zellen der Spalte G, Formeln drinstehen habe und Excel das wohl mit beachtet und diese Zellen auch nicht löscht selbst wenn kein Wert angezeigt wird, steht ja quasi eine Formel drinne :/
(Es sind Wenn-Formeln die einen Wert nur unter bestimmten Voraussetzungen anzeigen.)
Gibt es dazu auch eine Lösung?

Man kann auch im Nachgang die formel per VBA auf die betroffenen Zellen anwenden. Wenn es bei dir möglich ist, erst die Daten ohne Formel einzufügen, dann die leeren Zeilen zu löschen und erst dann die Formel auf die verbliebenen Inhalte anzuwenden, dann mach das so. Ohne zu googeln fehlt mir spontan keine Lösung ein, wie das anders erfolgen kann.

Ac3 · 26. Oktober 2017

Hi G-Red, mal eine andere Frage noch. ich könnte mein Problem über umwege lösen und das evtl auch ohne makro.

Gibt es eine Formel mit der man den erste, zweiten, dritten.... Wert einer Spalte ausgeben lassen kann?
Habe was von index bzw. Vergleich Formeln gelesen werde aber nicht schlau daraus ..

LG

G-Red · 26. Oktober 2017

Ac3 schrieb:
Hi G-Red, mal eine andere Frage noch. ich könnte mein Problem über umwege lösen und das evtl auch ohne makro.

Gibt es eine Formel mit der man den erste, zweiten, dritten.... Wert einer Spalte ausgeben lassen kann?
Habe was von index bzw. Verleich Formeln gelesen werde aber nicht schlau daraus ..

LG

Da musst du etwas präzise werden. Kannst du ein Beispiel geben, was du erreichen möchtest?

Ac3 · 26. Oktober 2017

Hier ein kurzes Beispiel. Ich möchte, dass meine "Problemzahlen" aus Spalte G "einfach" in Spalte H ausgelesen werden. zuerst der oberste wert von Spalte G dann der zweite Wert von Spalte G und so weiter. Hoffe, dass mein Bild weiterhilft. Ansonsten einfach bescheid geben und ich erkläre es genauer

Danke für deine Mühen G-Red

aroxx · 26. Oktober 2017

Noch zwei generelle Performancehinweise für VBA:

.value2 ist wesentlich schneller als .value
und
Application.Screenupdating = False
//mein Code
Application.Screenupdating = True
hilft auch ungemein

DPXone · 27. Oktober 2017

Ac3 schrieb:
Anhang anzeigen 648615

Hier ein kurzes Beispiel. Ich möchte, dass meine "Problemzahlen" aus Spalte G "einfach" in Spalte H ausgelesen werden. zuerst der oberste wert von Spalte G dann der zweite Wert von Spalte G und so weiter. Hoffe, dass mein Bild weiterhilft. Ansonsten einfach bescheid geben und ich erkläre es genauer

Danke für deine Mühen G-Red

In Zelle I2 folgende Formel einfügen, aber nicht einfach mit der ENTER-Taste bestätigen, sondern mit STRG+SHIFT+ENTER
Dadurch wird aus der Formel eine Matrix-Formel. Erkennbar an den geschweiften Klammern in der Formelleiste, wenn man die Zelle markiert.

Code:

=WENNFEHLER(INDEX($G$2:$G$500;KKLEINSTE(WENN($G$2:$G$500<>"";ZEILE($G$2:$G$500)-1);ZEILE()-1));"")

Danach einfach runter ziehen.
Das -1 hinter den beiden ZEILE(....) Funktionen muss angepasst werden, wenn der Beginn nicht in Zeile 2 (bei dir I2) stattfindet. (kompliziert zu erklären

)
Die oberste Formel (bei dir aktuell I2) muss als Ergebnis 1 auswerfen, wenn man ZEILE(...)-X berechnet. ( z. B. ZEILE(G55)-54 = 1 )

Beispiel im Anhang.

Hoffe ich hab die Formel ohne Fehler übersetzt. Verwende nämlich Windows + Office in Englisch.

Gameforce2 · 28. Oktober 2017

wie wäre es wenn Du die zeilen nicht löschst, sondern du Die Einträge einfach nach oben verschiebst. Wenn Du eine Zeile gefunden hast, die leer ist einfach die nächste gefüllte Zeile (zelle) dorthin kopierst. So verhinderst Du das die Löschfunktion so lange zeit verschwendet.

cumulonimbus8 · 28. Oktober 2017

@Ac3
Ich käme nie auf solche Ideen wie Cell(x,y).Delete und musste mir die Wirkungsweise dieses Kommandos ansehen.
Was soll das finale Ziel dieser Aktion sein?
Gemeinhin löscht man ganze Zeilen statt Zellen hochrutschen zu lassen.

Du könntest übrigens das Pferd von hinten aufzäumen. Und klar wiederhole ich hier die Kollegen:

Code:

Sub Täscht()
Dim A As Long
 Application.ScreenUpdating = False
 A = ActiveSheet.Cells(Rows.Count, 7).End(xlUp).Row - 1
 While A > 0
  If Cells(A, 7) = "" Then
   Cells(A, 7).Delete
   A = A - 1
  End If
  A = A - 1
 Wend
 Application.ScreenUpdating = True
End Sub

Die Minus ein da logischerweise eine gefüllte letzte Zelle gefunden wird (fällt beim Tempo aber nicht auf…). Das A-1 im IF ist die Zeile mit der Löschung, das andere ganz normales Hochzählen.
Wenn VBA dann pures VBA und keine Blattfunktionen

Am »Zielwert« einer FOR-Schleife sollte man nicht herumbasteln, das tust du ja indem du weglöschst und immer neu ermitteln lässt.

CN8

VBA Code schneller machen

Cadet 3rd Year

Lieutenant

Commander

Cadet 3rd Year

mac4life

Gast

Cadet 4th Year

Commander

Cadet 3rd Year

Commander

Cadet 4th Year

Commander

Cadet 3rd Year

Commander

Cadet 3rd Year

Commander

Cadet 3rd Year

Lieutenant

Lieutenant

Anhänge

Cadet 3rd Year

Fleet Admiral

Ähnliche Themen