Microsoft Excel

Herbers Excel/VBA-Archiv

8 Zeilen mit 195 Spalten - copy/einfügen - langsam

Betrifft: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam von: STeve
Geschrieben am: 31.01.2020 09:04:23

GuMo liebe Helfer......bräuchte wieder mal von euch Tipps/Hilfe!!.

Mit dem ua. Code per - Do Loop Until - kopiere ich
8 Zeilen die bis zur Spalte GM (= 195 Spalten) mit Formeln/Formatierung bepackt sind
diese
muss ich dann immer wieder unterhalb "einfügen". Bei den ersten 10 mal kein Problem - jedoch dann wird es immer langsamer.
Bei 20 mal bin ich schon bei 4 Minuten und bei den ca. geforderten 70 mal bin ich bei über 30 Minuten.

Insert Shift:=xlDown und Resize(8).Insert ---- machen das Richtige aber beides gleich langsam

.Rows(lngStart & ":" & lngStart + 7).Copy
wosollzeilejetzt = Application.Match("soll", Columns(1), 0)

'''''''' .Rows(wosollzeilejetzt).Insert Shift:=xlDown ''''''''''''''ca. 4 Minuten für 20 Blöcke
.Rows(wosollzeilejetzt).Resize(8).Insert ''''''''''''''ca. 4 Minuten für 20 Blöcke

Besten Dank.
mfg STeve

Betrifft: AW: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: Nepumuk
Geschrieben am: 31.01.2020 10:54:58

Hallo STeve,

hast du vor dem kopieren die automatische Berechnung von Formeln ausgeschaltet? Und falls du Event-Makros in der Mappe hast diese unterdrückt?

Public Sub Beispiel()
    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    'hier dein Code zum kopieren
    
    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

Gruß
Nepumuk

Betrifft: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: STeve
Geschrieben am: 31.01.2020 11:05:03

Hi lieber Nepumuk....Danke für deine Hilfe......SOOOOORRRRRRYYYYYY hab ich vergessen anzuführen........ja alles ausgeschalten und unterm Code wieder aktiviert.

Bitte um deine weitere Hilfe/Tipps!!!!

Betrifft: AW: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: ChrisL
Geschrieben am: 31.01.2020 12:56:55

Hi

https://www.herber.de/forum/messages/1737302.html

Wenn du den Code in einer Dummy-Datei ausführst (20 Blöcke x 8 Zeilen), dann läuft der Code weniger wie eine Sekunde.

Ich denke darum, es muss etwas anderes in deiner Datei sein, was den Prozess verlangsamt.

cu
Chris

Betrifft: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: STeve
Geschrieben am: 31.01.2020 14:02:39

Hallo Chris.........super dass dich meinem Problem- hast mir ja vor ein paar Tagen beim Grundcode bereits geholfen - angenommen hast. DANKE schon mal vorab.

Antwort:
Also dein Vorschlag wäre ein Dummy-Tabellenblatt wo ich die 70 Blöcke reinkopiere und dann alles auf einmal in das richtige Blatt kopiere???

Das Dummy-Blatt könnte ich ja danach löschen....

Das werde ich mal versuchen und schaue auf die Zeitersparnis.
Danke inzwischen.
lg STeve

Betrifft: AW: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: ChrisL
Geschrieben am: 31.01.2020 14:26:24

Hi

Ne da hast du mich falsch verstanden. In einem Test-Aufbau (Dummy-Datei) flutscht der Code recht schnell. Ich denke darum, dass das Problem nicht im diskutierten Code-Schnipsel sondern sonst wo in deiner Datei zu suchen ist.

Vielleicht werden die Events/Calculate zwar abgeschaltet, aber unterwegs (z.B. in einer Unterprozedur) irrtümlicherweise vorzeitig wieder eingeschaltet.

Schlussendlich kann man das Problem nur mittels genauer Analyse der Datei eruieren.

cu
Chris

Betrifft: AW: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: Daniel
Geschrieben am: 31.01.2020 15:08:29

ich habs grad im anderen Beitrag geschrieben:
das Problem ist, dass Excel beim Einfügen oder Löschen von Zeilen überprüft, ob es Formeln gibt, die auf den veränderten Zellbereich referenzieren und passt diese Formeln dann an.
Diese Aktion ist recht zeitaufwendig und lässt sich auch nicht abschalten.
Daher bringt auch das GetMoreSpeed an dieser Stelle nicht viel.

Gruß Daniel

Betrifft: danke, macht Sinn... owT.
von: ChrisL
Geschrieben am: 31.01.2020 15:42:18

.

Betrifft: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: STeve
Geschrieben am: 31.01.2020 15:52:53

An Chris:
Mit F8 wie immer getestet...kein Einschalten durch andere Prozedur.

Es liegt mit Sicherheit an der Riesendatenmenge bzw. dadurch dass er nach dem Einfügen alle Formeln anpasst (wie von Daniels Antwort::::::............ ob es Formeln gibt, die auf den veränderten Zellbereich referenzieren und passt diese Formeln dann an. Diese Aktion ist recht zeitaufwendig und lässt sich auch nicht abschalten.
Daher bringt auch das GetMoreSpeed an dieser Stelle nicht viel................... bestätigt)

Die Orginaldatei hat bereits -aufgrund der vielen ExcelFormeln - 5,2 MB

Schade!!!! .....dachte mir einer von euch VBA Könnern stellt mir einen Einzeiler_Code zu Verfügung ;-)
der schnell funzt.

lg STeve

Betrifft: AW: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: Daniel
Geschrieben am: 31.01.2020 16:09:40

naja hab ich doch:

einfach zu beginn einmalig genügend Leerzeilen einfügen, so dass du die Daten problemlos mit Copy-Paste übertragen kannst. Beim Copy-Paste hilft auch wieder das GetMoreSpeed, weil das nur Neuberechung und Events auslöst, und das ist ja abschaltbar.
hinterher dann die überflüssigen Leerzeilen wieder löschen.

das sind dann zwar nicht eine, sondern zwei Zeilen und sollte die Sache beschleunigen.

ansonsten ist es etwas naiv, anzunehmen dass man bei solchen Datenmengen mit einer einzigen Zeile bzw einer Einstellungsänderung viel bewirken kann.
Hier muss man, wenn man hohe Verarbeitungsgeschwindigkeiten erreichen will, sich ein bisschen mit der Arbeitsweise von Excel auseinander setzen, um den optimalen Weg zu seinem Ziel zu finden.

Gruß Daniel

Betrifft: AW: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: Daniel
Geschrieben am: 31.01.2020 13:02:36

Hi

Warum "unterhalb einfügen" (insert)?
Versuchs doch mal mit einfachem copy-paste

Oder hast du ganz unten noch eine Ergebniszeile oder weitere Daten, die entsprechend verschoben werden müssen?

Wenn

Betrifft: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: STeve
Geschrieben am: 31.01.2020 14:06:12

Hi Daniel......danke für deine Hilfe schon mal vorab.

Richtig.....darunter habe ich nochmal 22 Zeilen die mit Formeln usw. befüllt sind.
Deshalb muss ich das dazwischen "einfügen" und der 22-Zeilen-Block muss nach unten verschoben werden.

Copy/Paste - geht sehr schnell aber glaube ich würde ja überschreiben!!!!

Bitte weiter um deine Hilfe!!!!
glg STeve

Betrifft: AW: 8 Zeilen mit 195 Spalten - copy/einfügen - langsam
von: Daniel
Geschrieben am: 31.01.2020 15:03:36

Hi
das Problem ist, dass beim Verschieben Excel jedes mal prüfen muss, ob Formeln vorhanden sind, die Zellbereiche verwenden, die auf Grund der Verschiebung angepasst werden müssen.
Dieser Aktion wird immer ausgeführt und ist nicht optional, daher hilft dir auch das GetMoreSpeed nicht viel.

ich sehe da folgende Lösungsmöglichkeiten:
a) du sammelst die einzufügenden Zellen an einer freien stelle per Copy-Paste, so dass du dann in einem Schritt als gesamten Block an der richtigen Stelle einfügen kannst (wurde glaube ich auch schon vorgeschlagen)
b) du lässt vorab eine Schleife laufen, die ermittelt wieviele Zeilen du insgesamt benötigst, so dass du zu beginn einmalig genügend Leerzeilen einfügen kannst, und die Daten mit einfachem Copy-Paste übertragen kannst
c) falls es möglich ist, die Formeln des 22er-Blocks nachträglich per Code zu erzeugen, überschreibe sie einfach und stelle die Formeln am Ende per Code wieder her.

Gruß Daniel

Betrifft: Danke ...an Chris und Daniel
von: STeve
Geschrieben am: 31.01.2020 16:02:11

Ok lieber Daniel......danke für deine Vorschläge.


Klingen alle sehr gut und werde ich in der Reihenfolge:
1.) nach Auszählen/Ermittlung der Leerzeilen und diese Einfügen -
2.) den 22er Block ausschneiden nach unten einfügen
3.) mit Copy/Paste die 8-Zeilen-Blöcke "dazwischen" einfügen.

Werde das am Montag versuchen!!!!!

Danke nochmal an dich und Chris..........super dass ihr immer da seid und so schnelle und gewaltige Hilfe leistet.

Wünsch euch noch ein schönes WE und lg
STeve

Betrifft: AW: Danke ...an Chris und Daniel
von: Daniel
Geschrieben am: 31.01.2020 16:42:45

Prüfe auf jeden Fall, ob Formeln im 22er-Block auch wunschgemäß angepasst werden.
hier kann man ggf über die Auswahl der verschobenen Zellen bestimmen, ob die Formeln angepasst werden oder nicht.
ansonsten, wenn du die Gesamtanzahl der notwendigen Leerzeilen kennst und diese in einem Schritt einfügen kannst, dann kannst du auch problemlos mit dem Einfügen der Zeilen arbeiten.
Solange du das nur einmal ausführst, ist es ja zeitunkritisch. Problematisch ist ja die mehrfache Ausführung.

Gruß Daniel

Beiträge aus dem Excel-Forum zum Thema "8 Zeilen mit 195 Spalten - copy/einfügen - langsam"