Guten Tag Liebe Community,
ich hätte folgendes Problem bzw. Rückfrage.
Ich habe eine Tabelle mit der befüllten Daten in Spalte A bis G.
Die Spalte A enthält eine variable Anzahl von einkopierten Inhalten, die sich von Zeile 5 bis 10.005 erstrecken können.
In Spalte B bis G befinden sich Formeln, die im ersten Moment von Zeile 1 in Zeile 5 kopiert werden und dann in jede Zeile kopiert werden sollen in denen sich ein Wert in Spalte A befindet.
Derzeit werden in meinen Code pauschal alle 10.000 Zeilen bedient:
Code:
Range("B1:G1").Select
Selection.Copy
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B5:G10005"), Type:=xlFillDefault
Range("B5:G18005").Select
Mit was für einen Formelabschnitt könnte ich nun die Performance des Makros verbessern und muss ich hierbei auf eventuelle Versionsunterschiede von Excel 2016 u. 365 achten?
Vielen Dank für eure Unterstützung.
BG
MaGleV
Hallo,
Formeln im Vorhinein in eine Liste zu schreiben, ist kein guter Ansatz. Es gibt mindestens zwei Möglichkeiten, das zu vermeiden:
1. Wenn Sie neue Zeilen mit Daten am Ende einer Liste hinzufügen, werden von Excel auf die neuen Zeilen konsistente Formatierungen und Formeln übertragen. Damit ein Format erweitert wird, müssen drei von fünf vorangegangenen Zellen dasselbe Format verwenden. Damit eine Formel erweitert wird, müssen alle vorangegangenen Formeln konsistent sein. (Aus der Excelhilfe)
2. du legst deine Liste als "Intelligente Tabelle" an, auch dann tragen sich die Formeln automatisch in jede neue Zeile ein.
(03.10.2020, 17:05)MaGleV schrieb: [ -> ]Derzeit werden in meinen Code pauschal alle 10.000 Zeilen bedient:
... und das ist gut so, wie auch Klaus-Dieter schrieb.
Denn Excel verwaltet Blöcke von Gleichartigem viel besser, als wenn es auseinandergerissen ist. Du würdest also eher die Formel um eine entsprechende Behandlung erweitern - und weil so etwas doof ist (!), unterlässt Du einfach die Lücken in den Daten. Und schon sind wir mit der Predigt am Ziel.
Hallo MaGleV, :19:
hier mal ein Beispiel (
das wäre dann das Wort zum Sonntag): :21:
[
attachment=34493]
Du kannst Formeln auf
einen Rutsch in
ganze Bereiche schreiben. Ohne das "
Wenn..." kannst du auch mit
SpecialCells arbeiten.
Oder du hältst dich an die Angaben meiner Vorredner.
Erst einmal vielen Dank für die Rückmeldungen.
Mein Problem liegt hauptsächlich in der Performance.
Ich habe die Datei bereits mit weiteren Formeln erweitert und die Möglichen Inhalte der Spalte A im Umfang erweitert.
(Formeln von Spalte B bis ca. AZ / Inhalt Spalte A bis Zeile 20.000)
Des Weiteren ist es gewollt, dass die Datei in Ihrer ursprünglichen Form relativ klein ist, deswegen sollen die Formeln auch erst im Nachgang auf die eingespielten Daten (Spalte A) via Makro angelegt werden.
Nun habe ich bereits jetzt schon das Dilemma, dass auf einem schwachen Rechner mein Makro, den Prozessor schon leicht in die Knie zwingt.
Gibt es hier eventuell die Möglichkeit Excel dazu zu bewegen jede Spalte einzeln "abzuarbeiten" um eine mögliche "Systemüberforderung" zu umgehen?
@Case : Vielen Dank für deine Datei aber meine Excel Künste reichen derzeit nicht aus um dein Makro Befehl so umzuschreiben, dass ich es effektiv für mein Makro nutzen könnte. (Kompilierungsfehler) :/
Code:
Ausschnitt:
Range("B1:G1").Select
Selection.Copy
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("B5:AZ20005"), Type:=xlFillDefault
Range("B5:AZ20005").Select
Hallo MaGleV, :19:
da hätte ich eine durchaus gewagte Idee - lade doch eine Beispieldatei mit den Formeln und vom Aufbau her dem Original gleich hier hoch. :21:
Guten Tag ,
@Case das klingt natüüürlich sehr plausibel. ^^
Anbei der versimpelte Dateiausschnitt.
Wäre super, wenn das Makro wirklich nur die Zeilen bedient, die durch einen anderen Schritt in Spalte A des Reiters Tabelle auch tatsächlich befüllt werden.
Hallo, :19:
bezogen auf
deine Beispieldatei: :21:
[
attachment=34505]
"
1, paar zerquetschte Sekunden" für 25.000 Zeilen und Spalte B:AZ.
Hi Case,
vielen Dank für deine Mühen.
Der folgende Abschnitt ist genau das was ich benötigt habe und lässt sich anscheinend problemlos in meinen Code via Copy Paste übernehmen.
Code:
With Tabelle1
.Range("B3:AZ" & Tabelle1.Cells(Rows.Count, 1).End(xlUp).Row).ClearContents
.Range("B1:AZ1").Copy .Range("B3:AZ" & Tabelle1.Cells(Rows.Count, 1).End(xlUp).Row)
Application.CutCopyMode = False
End With
Bitte helf mir noch ein letztes mal aufs Pferd.
Wieso kann Excel die Bezeichnung "Tabelle1" verwenden? Der Reiter/ das Tabellenblatt heist doch "Tabelle" oder ist diese Bezeichnung abhängig vom Active Sheet?
Hallo, :19:
"
Tabelle1" ist der
CodeNmae eines
Tabellenblattes in einem deutschen Excel - in Englisch "
Sheet1". Schau mal im VBA Editor im linken Fenster steht bei dir "
Tabelle1(Tabelle)" und "
Tabelle2(Makroschaltfläche)".
Das
vor der Klammer ist der
Codename, das in der Klammer der
Name in den du die Tabelle
umbenennen kannst.
Es ist
bequemer und
sicherer mit dem
Codename zu arbeiten, denn der
klappt auch dann noch, wenn du das Tabellenblatt
umbenennst, oder verschiebst (
falls du mit dem Index arbeitest).
Du könntest auch so schreiben: :21:
Code:
With ThisWorkbook.Worksheets("Tabelle")
Das klappt aber nicht mehr, wenn du umbenennst.