Thema: Datenmenge für Makro zu groß

Hallo zusammen,
ich verwende folgendes Makro, um an mehreren Stellen im Tabellenblatt neue Werte gleichzeitig einzufügen. Neben einer zu ändernden Bezeichnung in Spalte C (die auch auf einem Verweis aus einem anderen Blatt beruht) habe ich in allen folgenden Spalten Formeln hinterlegt. Daher ist es für mich die praktikabelste Lösung, die Zellen einfach zu kopieren und dann eben den Bezug in Spalte C jeweils zu ändern, um die korrekte Bezeichnung zu erhalten.
Leider funktioniert das Makro nur bei einer kleinen Datenmenge.
Hat vielleicht jemand eine Idee, was ich ändern könnte damit es schneller läuft?

Sub Werteeinfügen()
Dim strInput As String
Dim lngRow As Long
strInput = InputBox("Bitte Einfügeort eingeben")
If StrPtr(strInput) <> 0 Then
For lngRow = Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1
If Cells(lngRow, 3).Text = strInput Then _
Call Rows(lngRow).Resize(1).Copy
Call Rows(lngRow).Resize(1).Insert(Shift:=xlShiftDown)
Application.CutCopyMode = False
Next lngRow
End If
End Sub

Danke und Gruß Timo

Hi
das Hauptproblem dürfte sein, dass jedes Einfügen eine neuberechnung der Formeln auslöst.
es sollte schneller gehen, wenn du die automatische neuberechnung für das Einfügen abschaltest.
Auch reicht es aus, wenn erst am Schluss das vollständige Ergebnis angezeigt wird, dh solange das Makro läuft, muss der Bildschirm nicht ständig aktualisiert werden.

Sub Werteeinfügen()
Dim strInput As String
Dim lngRow As Long
strInput = InputBox("Bitte Einfügeort eingeben")
If StrPtr(strInput) <> 0 Then
Application.ScreenUpdating = false
Application.Calculation = xlCalulationManual
For lngRow = Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1
If Cells(lngRow, 3).Text = strInput Then _
Call Rows(lngRow).Resize(1).Copy
Call Rows(lngRow).Resize(1).Insert(Shift:=xlShiftDown)
Application.CutCopyMode = False
Next lngRow
Application.Calculation = xlcalculationautomatic
Application.ScreenUpdating = True
End If
End Sub

Gruß Daniel

Hi Daniel,
vielen Dank für deine Antwort. Leider hat sich das Programm erneut aufgehängt..
Es kam dann die Meldung "Intersect für das Objekt-Global ist fehlgeschlagen".
Danke und Gruß,
Timo

Hi
das ist insbesondere deswegen erstaunlich, weil "intersect" im Code ja gar nicht vorkomm!
Gruß Daniel

Hallo Daniel,
ja, ich vermute das kommt von diesem Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Zelle As Range
If Not Intersect(Range("I1:EG800"), Target) Is Nothing Then
For Each Zelle In Intersect(Range("I1:EG800"), Target)
With Zelle
If .Value = "----" Then .Font.Size = 16
If .Value = "---" Then .Font.Size = 16
If .Value = "===" Then .Font.Size = 12
If .Value = "xxxx" Then .Font.Size = 11
End With
Next Zelle
End If
End Sub

Könnte das Problem auftreten, wenn Zellen verändert werden sollen, die einen der im Code defnierten Werte enthalten?
Danke und Gruß,
Timo

hi
kann viel passieren.
muss dieses Makro ausgeführt werden, wenn dein andere Code läuft?
wenn nein, schau dir mal den Befehl: Application.EnableEvents an.
damit kann man die Ausführung der Eventmakros verhindern, wenn sie nicht erforderlich sind und den Programmablauf zusätzlich beschleunigen.
Gruß Daniel

Hallo Daniel,
danke für deinen Vorschlag. Es muss nicht ausgeführt und habe das auch so ausprobiert. Das Worksheet_Change Ereignis lief dann nicht, jedoch hatte es leider keine Auswirkungen auf das andere Makro.
Weißt du evtl. noch von weiteren „typischen“ Fehlerquellen in großen Excel-Dateien? Liegt ja dann vermutlich nicht an der VBA-Programmierung.
Danke und Gruß
Timo

Hi
grosse Exceltabellen sollte man nicht in Schleifen über einzelne Excelzellen bearbeiten.
insbesondere dann nicht, wenns ums einfügen oder Löschen von Zeilen geht.
hier sollte man immer dafür sorgen, dass man dann die Zeilen möglichst in einem Schritt als lückenlosen Block bearbeit.
hierbei kann man ausnutzen, dass Excel im Sortieren auch bei grossen Datenmengen sehr schnell ist.
in deinem Fall würde ich das so lösen, wenn ich von bestimmte Zeilen kopieren unterhalb der Ausgangszeile anlegen will:
1. in der Ausgangstabelle in einer Hilfsspalte die Originalreihenfolge sichern, indem ich dort die Zeilennummer oder eine aufsteigende Zahl eintrage (wie das geht, brauche ich einem Excelprofi nicht zu erklären)
2. dann sortiere ich die Liste nach Spalte C, so dass alle Zeilen, die ich suche, einen lückenlosen Block bilden.
3. dann suche ich die erste und letze Zeile in Spalte C mit dem entsprechenden Suchbegriff (kann man in .Find über den Parameter: Searchdirection:=xlNext/xlPrevious steueren
4. dann kopiere ich alle zeilen zwischen dem ersten und dem letzen Auftreten des Suchbegriffs (Tabelle ist ja entsprechend sortiern und füge sie nach der letzen Zeile wieder ein.
5. dann sortiere ich die ganze Tabelle nach der Hilfsspalte mit den gesicherten Zeilennummern.
damit werden die Zeilen wieder in die originalreihenfolge gebracht und die neu hinzugefügten Zeilen werden unter ihre "originale" sortiert, weil sie die gleiche Sortiernummer haben.
das verfachen sollte auch mit grössten Datenmengen schnell und zuverlässig funktioniern.
man kann das auch in VBA dann relativ einfach mit hilfe des Recorder umsetzen.
Gruß Daniel

Hallo Daniel,
vielen Dank. Das funktioniert gut.
Mit dem Makro-Recorder komm ich allerdings nicht ganz so weit. Ich müsste ja den Zellbereich bzw. die Werte, die ich kopieren möchte irgendwie durch eine Inputbox übergeben? Das ist auch insbesondere deshalb wichtig, weil das Einfügen neuer Zeilen durch eine einzige neue Zeile in einer Mastersicht auf einem anderen Tabellenblatt ausgelöst wird. Der Wert einer solchen Zelle kommt dann im neuen Blatt mehrmals vor und daher wär die Suche mit einer Inputbox ideal.
Danke und Gruß Timo

Hallo Timo!
Wieviele Datenzeile sind es maximal?
Wieviele Spalten sind es(einschl. der Formeln), Spalte A bis Spalte ..?
Gruß Gerd

Hallo zusammen,
ich verwende folgendes Makro, um an mehreren Stellen im Tabellenblatt neue Werte gleichzeitig einzufügen. Neben einer zu ändernden Bezeichnung in Spalte C (die auch auf einem Verweis aus einem anderen Blatt beruht) habe ich in allen folgenden Spalten Formeln hinterlegt. Daher ist es für mich die praktikabelste Lösung, die Zellen einfach zu kopieren und dann eben den Bezug in Spalte C jeweils zu ändern, um die korrekte Bezeichnung zu erhalten.
Leider funktioniert das Makro nur bei einer kleinen Datenmenge.
Hat vielleicht jemand eine Idee, was ich ändern könnte damit es schneller läuft?

Sub Werteeinfügen()
Dim strInput As String
Dim lngRow As Long
strInput = InputBox("Bitte Einfügeort eingeben")
If StrPtr(strInput) <> 0 Then
For lngRow = Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1
If Cells(lngRow, 3).Text = strInput Then _
Call Rows(lngRow).Resize(1).Copy
Call Rows(lngRow).Resize(1).Insert(Shift:=xlShiftDown)
Application.CutCopyMode = False
Next lngRow
End If
End Sub

Danke und Gruß Timo

Hi
das Hauptproblem dürfte sein, dass jedes Einfügen eine neuberechnung der Formeln auslöst.
es sollte schneller gehen, wenn du die automatische neuberechnung für das Einfügen abschaltest.
Auch reicht es aus, wenn erst am Schluss das vollständige Ergebnis angezeigt wird, dh solange das Makro läuft, muss der Bildschirm nicht ständig aktualisiert werden.

Sub Werteeinfügen()
Dim strInput As String
Dim lngRow As Long
strInput = InputBox("Bitte Einfügeort eingeben")
If StrPtr(strInput) <> 0 Then
Application.ScreenUpdating = false
Application.Calculation = xlCalulationManual
For lngRow = Cells(Rows.Count, 3).End(xlUp).Row To 1 Step -1
If Cells(lngRow, 3).Text = strInput Then _
Call Rows(lngRow).Resize(1).Copy
Call Rows(lngRow).Resize(1).Insert(Shift:=xlShiftDown)
Application.CutCopyMode = False
Next lngRow
Application.Calculation = xlcalculationautomatic
Application.ScreenUpdating = True
End If
End Sub

Gruß Daniel

Hi Daniel,
vielen Dank für deine Antwort. Leider hat sich das Programm erneut aufgehängt..
Es kam dann die Meldung "Intersect für das Objekt-Global ist fehlgeschlagen".
Danke und Gruß,
Timo

Hi
das ist insbesondere deswegen erstaunlich, weil "intersect" im Code ja gar nicht vorkomm!
Gruß Daniel

Hallo Daniel,
ja, ich vermute das kommt von diesem Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Zelle As Range
If Not Intersect(Range("I1:EG800"), Target) Is Nothing Then
For Each Zelle In Intersect(Range("I1:EG800"), Target)
With Zelle
If .Value = "----" Then .Font.Size = 16
If .Value = "---" Then .Font.Size = 16
If .Value = "===" Then .Font.Size = 12
If .Value = "xxxx" Then .Font.Size = 11
End With
Next Zelle
End If
End Sub

Könnte das Problem auftreten, wenn Zellen verändert werden sollen, die einen der im Code defnierten Werte enthalten?
Danke und Gruß,
Timo

hi
kann viel passieren.
muss dieses Makro ausgeführt werden, wenn dein andere Code läuft?
wenn nein, schau dir mal den Befehl: Application.EnableEvents an.
damit kann man die Ausführung der Eventmakros verhindern, wenn sie nicht erforderlich sind und den Programmablauf zusätzlich beschleunigen.
Gruß Daniel

Hallo Daniel,
danke für deinen Vorschlag. Es muss nicht ausgeführt und habe das auch so ausprobiert. Das Worksheet_Change Ereignis lief dann nicht, jedoch hatte es leider keine Auswirkungen auf das andere Makro.
Weißt du evtl. noch von weiteren „typischen“ Fehlerquellen in großen Excel-Dateien? Liegt ja dann vermutlich nicht an der VBA-Programmierung.
Danke und Gruß
Timo

Hi
grosse Exceltabellen sollte man nicht in Schleifen über einzelne Excelzellen bearbeiten.
insbesondere dann nicht, wenns ums einfügen oder Löschen von Zeilen geht.
hier sollte man immer dafür sorgen, dass man dann die Zeilen möglichst in einem Schritt als lückenlosen Block bearbeit.
hierbei kann man ausnutzen, dass Excel im Sortieren auch bei grossen Datenmengen sehr schnell ist.
in deinem Fall würde ich das so lösen, wenn ich von bestimmte Zeilen kopieren unterhalb der Ausgangszeile anlegen will:
1. in der Ausgangstabelle in einer Hilfsspalte die Originalreihenfolge sichern, indem ich dort die Zeilennummer oder eine aufsteigende Zahl eintrage (wie das geht, brauche ich einem Excelprofi nicht zu erklären)
2. dann sortiere ich die Liste nach Spalte C, so dass alle Zeilen, die ich suche, einen lückenlosen Block bilden.
3. dann suche ich die erste und letze Zeile in Spalte C mit dem entsprechenden Suchbegriff (kann man in .Find über den Parameter: Searchdirection:=xlNext/xlPrevious steueren
4. dann kopiere ich alle zeilen zwischen dem ersten und dem letzen Auftreten des Suchbegriffs (Tabelle ist ja entsprechend sortiern und füge sie nach der letzen Zeile wieder ein.
5. dann sortiere ich die ganze Tabelle nach der Hilfsspalte mit den gesicherten Zeilennummern.
damit werden die Zeilen wieder in die originalreihenfolge gebracht und die neu hinzugefügten Zeilen werden unter ihre "originale" sortiert, weil sie die gleiche Sortiernummer haben.
das verfachen sollte auch mit grössten Datenmengen schnell und zuverlässig funktioniern.
man kann das auch in VBA dann relativ einfach mit hilfe des Recorder umsetzen.
Gruß Daniel

Hallo Daniel,
vielen Dank. Das funktioniert gut.
Mit dem Makro-Recorder komm ich allerdings nicht ganz so weit. Ich müsste ja den Zellbereich bzw. die Werte, die ich kopieren möchte irgendwie durch eine Inputbox übergeben? Das ist auch insbesondere deshalb wichtig, weil das Einfügen neuer Zeilen durch eine einzige neue Zeile in einer Mastersicht auf einem anderen Tabellenblatt ausgelöst wird. Der Wert einer solchen Zelle kommt dann im neuen Blatt mehrmals vor und daher wär die Suche mit einer Inputbox ideal.
Danke und Gruß Timo

Hallo Timo!
Wieviele Datenzeile sind es maximal?
Wieviele Spalten sind es(einschl. der Formeln), Spalte A bis Spalte ..?
Gruß Gerd