Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Makro Zeilen einfügen -> Excelabsturz
06.11.2013 09:42:17
Marie
Liebes Forum,
ich habe eine Exceldatei, in die ich Daten per copy und paste in den Bereich von Spalte D bis LZ einfüge (ab Zeile 21) und in den Folgespalten eine Menge Werte über verschiedene Formeln berechne. Da die Datei aufgrund der Datenmengen schnell behäbig wird, möchte ich über ein Makro immer nur so viele Zeilen mit Formeln haben, wie ich auch jeweils im Datensatz habe (ich berechne jeweils für einen Datensatz).
Die Datensätze haben zwischen 200 und 20.000 Zeilen.
Ich habe bereits ein Makro (s. unten), mit dem ich neue Zeilen inkl. Formeln einfügen kann, allerdings stürzt Excel jedesmal ab, wenn ich versuche, mehr als 1.000 Zeilen einzufüge :-(
Kann mir da vllt jemand helfen...?
Vielen Dank schon mal im voraus!!
Sub ZeilenEinfügenUndFormelnBehalten()
Application.ScreenUpdating = False
Dim anzahlZeilen, EinfuegenAbZeile, ZeileKopieren As Integer
anzahlZeilen = InputBox("Wieviele Zeilen sollen eingefügt werden?")
Range("D21:LZ21").Clear
ZeileKopieren = InputBox("Welche Zeile soll kopiert werden?", , 21)
EinfuegenAbZeile = InputBox("An welcher Stelle sollen die Zeilen eingefügt werden?", , 21)
For i = 1 To anzahlZeilen
Rows(EinfuegenAbZeile).Insert Shift:=xlDown
Next
Rows(ZeileKopieren).Copy
For i = 1 To anzahlZeilen
Rows(EinfuegenAbZeile + i - 1).PasteSpecial xlPasteFormulas
Next
End Sub

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Makro Zeilen einfügen -> Excelabsturz
06.11.2013 10:05:59
Bastian
Hallo Marie,
versuchs mal ohne Schleife:
Option Explicit
Sub ZeilenEinfügenUndFormelnBehalten()
Dim anzahlZeilen As Integer, EinfuegenAbZeile As Integer, ZeileKopieren As Integer
anzahlZeilen = InputBox("Wieviele Zeilen sollen eingefügt werden?")
Range("D21:LZ21").Clear
ZeileKopieren = InputBox("Welche Zeile soll kopiert werden?", , 21)
EinfuegenAbZeile = InputBox("An welcher Stelle sollen die Zeilen eingefügt werden?", , 21)
Rows(ZeileKopieren).Copy
Rows(EinfuegenAbZeile & ":" & EinfuegenAbZeile + anzahlZeilen - 1).Insert Shift:=xlDown
End Sub

Übrigens:
Wenn Du so die Variablen deklarierst:

Dim anzahlZeilen, EinfuegenAbZeile, ZeileKopieren As Integer
sind anzahlZeilen und EinfuegenAbZeile vom Typ Variant statt Integer.
Gruß, Bastian

Anzeige
AW: Makro Zeilen einfügen -> Excelabsturz
06.11.2013 10:29:49
Marie
Hallo Bastian,
vielen Dank erstmal, das ging ja blitzschnell :-)
Ich habe dein Makro eben ausprobiert, aber leider enthalten die Zeilen, die nun eingefügt werden, keine Formeln, sondern sind komplett leer :-( Muss ich einen Teil des "alten" Makros stehen lassen? Leere Zeilen einzufügen hilft nämlich leider nicht :-(
Merci
Marie

AW: Makro Zeilen einfügen -> Excelabsturz
06.11.2013 10:31:27
Bastian
Hallo Marie,
lass mal die Zeile
Range("D21:LZ21").Clear
weg.
Gruß, Bastian

AW: Makro Zeilen einfügen -> Excelabsturz
06.11.2013 10:55:35
Marie
Huhu Bastian,
hilft leider nicht; außerdem brauche ich diesen Teil, damit noch vorhandene Daten direkt rausgeworfen werden, sonst übersehe ich die eventuell und dann stimmt die Rechnung nicht mehr.
Verstehe auch nicht, warum er die Formeln nicht mehr kopiert :-(

Anzeige
AW: Makro Zeilen einfügen -> Excelabsturz
06.11.2013 11:02:02
Bastian
Hallo Marie,
Du hast ja als default die Zeile 21 in der Msgbox.
Wenn Du als zu kopierende Zeile die 21 wählst, aber vorher die Werte (und Formeln) löschst, wird natürlich auch nichts eingefügt.
Kannst Du die Datei hochladen? Mir fällt im Moment kein Grund ein, warum es nicht funktionieren sollte.
Gruß, Bastian

AW: Makro Zeilen einfügen -> Excelabsturz
06.11.2013 12:46:18
fcs
Hallo Marie,
dein Makro und auch die Version von Bastian haben ein Handycap.
Wenn für "EinfuegenabZeile" der geiche oder ein kleinerer Wert als für "ZeileKopieren" gewählt wird also z.B. die Defaultwerte 21 übernommen werden, dann wird die zu kopierende Zeile beim Einfügen der Leerzeilen nach unten verschoben. Beim anschließenden Kopieren wird dann ggf. eine leere Zeile kopiert.
Das kann man umgehen, indem man den 2. Defaultwert auf 22 ändert.
Noch sichere ist nachfolgende Anpassung, die die zu kopierende Zeile einer Objektvariablen zuweist.
Gruß
Franz
Sub ZeilenEinfügenUndFormelnBehalten()
Dim anzahlZeilen, EinfuegenAbZeile, ZeileKopieren
Dim rngCopy As Range
Dim i As Long
anzahlZeilen = Application.InputBox( _
"Wieviele Zeilen sollen eingefügt werden?", _
Type:=1)
If anzahlZeilen = False Then Exit Sub
Range("D21:LZ21").Clear
ZeileKopieren = Application.InputBox( _
"Welche Zeile soll kopiert werden?", _
Default:=21, Type:=1)
If ZeileKopieren = False Then Exit Sub
EinfuegenAbZeile = Application.InputBox( _
"An welcher Stelle sollen die Zeilen eingefügt werden?", _
Default:=21, Type:=1)
If EinfuegenAbZeile = False Then Exit Sub
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set rngCopy = Rows(ZeileKopieren)
Range(Rows(EinfuegenAbZeile), _
Rows(EinfuegenAbZeile + anzahlZeilen - 1)).Insert Shift:=xlDown
rngCopy.Copy
Range(Rows(EinfuegenAbZeile), _
Rows(EinfuegenAbZeile + anzahlZeilen - 1)).PasteSpecial xlPasteFormulas
With Application
.ScreenUpdating = Ture
.Calculation = xlCalculationAutomatic
End With
End Sub

Anzeige
AW: Makro Zeilen einfügen -> Excelabsturz
06.11.2013 13:06:05
Bastian
Hallo Franz,
die zu kopierende Zeile wird doch vor dem Einfügen kopiert (zumindest in meiner Version).
Also kann auch die Zeile, ab der eingefügt wird über der Zeile liegen, die kopiert wurde.
Gruß, Bastian

AW: Makro Zeilen einfügen -> Excelabsturz
06.11.2013 13:45:15
Der
Hallo Bastian,
stimmt, dein Makro fuktioniert auch korrekt.
Bleibt nur die Frage, ob Marie beim kopieren unbedingt erst Leerzeilen einfügen und anschließend nur die Formeln und Werte kopieren möchte.
Gruß
Franz

AW: Makro Zeilen einfügen -> Excelabsturz
07.11.2013 09:50:31
Marie
Huhu,
also, die Zeile wird ja nicht gelöscht, es wird nur der Bereich von D bis LZ von den Datenwerten "befreit". Die Formeln stehen erst in den Spalten dahinter. Mein Makro funktioniert grundsätzlich auch, das Problem ist nur, wenn ich versuche, mehr als 1.000 Zeilen einzufügen, stürtzt Excel leider ab. Das Makro, das Bastian netterweise gepostet hat, funktioniert auch, hat bei mir aber leider die gesamte Zeile gelöscht...
LG

Anzeige
AW: Makro Zeilen einfügen -> Excelabsturz
07.11.2013 11:10:27
Bastian
Hallo Marie,
soll heißen es funktioniert jetzt? Auch bei mehr als 1000 eingefügten Zeilen?
Die Zeile:
Range("D21:LZ21").Clear
hab ich so aus Deinem Makro übernommen. Da wird keine ganze Zeile gelöscht.
Gruß, Bastian

AW: Makro Zeilen einfügen -> Excelabsturz
07.11.2013 14:55:14
fcs
Hallo Marie,
Bastians Makro und auch mein Vorschlag kopieren korrekt. Formeln die rechts von Spalte LZ stehen werden korrekt kopiert.
Das Problem bei deinem Makro ist, dass sich die Laufzeit des Makros durch ständiges Neuberechnen der Datei bei jedem Einfügen einer Zeile extrem lang wird.
Deshalb sollte während des Einfügens und Kopierens der Zeilen der Berechnungsmodus auf "manuell" gesetzt werden.
Falls du auch noch Ereignismakros in der Datei hast dann sollten auch diese temporär deaktiviert werden.
Gruß
Franz
Hier dein Makro mit den entsprechenden Anpassungen. Den Defaultwert für die Einfügezeile hab ich auf 22 geändert, da sonst Zeile 21 beim Einfügen von Zeilen verschoben werden kann und dann hast du in den kopierten Zeilen keine Daten/Formeln mehr.
Sub ZeilenEinfügenUndFormelnBehalten_Marie()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Dim anzahlZeilen, EinfuegenAbZeile, ZeileKopieren As Integer
anzahlZeilen = InputBox("Wieviele Zeilen sollen eingefügt werden?")
Range("D21:LZ21").Clear
ZeileKopieren = InputBox("Welche Zeile soll kopiert werden?", , 21)
EinfuegenAbZeile = InputBox("An welcher Stelle sollen die Zeilen eingefügt werden?", , 22)
For i = 1 To anzahlZeilen
Application.StatusBar = "Einfügen Zeile " & i & " von " & anzahlZeilen
Rows(EinfuegenAbZeile).Insert Shift:=xlDown
Next
Rows(ZeileKopieren).Copy
For i = 1 To anzahlZeilen
Application.StatusBar = "Kopieren Zeile " & i & " von " & anzahlZeilen
Rows(EinfuegenAbZeile + i - 1).PasteSpecial xlPasteFormulas
Next
With Application
.CutCopyMode =False
.StatusBar = False
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
MsgBox "Fertig"
End Sub

Anzeige
AW: Makro Zeilen einfügen -> Excelabsturz
07.11.2013 17:47:59
Marie
Hallo Bastian,
ich weiß auch nicht, was da gestern los war, irgendwas habe ich da wohl vermurkst- dein Makro läuft jedenfalls ganz wunderbar, auch bei mehreren tausend Zeilen, die eingefügt werden müssen.
Vielen Dank!!
@Franz: Das Makro, das du gepostet hast (ebenfalls merci natürlich :-)) läuft- genau wie meins- prima für 100 Zeilen, aber bei 1.000 oder mehr stürtzt Excel leider bei mir ab.
Aber jetzt läufts ja, danke euch!!

319 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige