Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1500to1504
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

AutoFilter - Datenbezug zu komplex!

AutoFilter - Datenbezug zu komplex!
23.06.2016 01:45:02
Noureddine
Hallo,
Bin mal das Thema AutoFilter mal kurz durch gegangen.
Folgendes Makro habe ich erstell:

Sub AutoFilter()
Dim Monat As String
Dim MonatVergleich As String
Dim LetzteZeileC As Integer
LetzteZeileC = Sheets("Calcs").Cells(Rows.Count, 1).End(xlUp).Rows.Row
Monat = InputBox("asasa", "dwas")
MonatVergleich = InputBox("asasa", "dwas")
'Tabellenblatt hinzufügen
Sheets.Add , Sheets(Sheets.Count)
ActiveSheet.Name = "Calcs2"
'Filter setzen
With Sheets("Calcs")
Range(.Cells(1, 1), .Cells(LetzteZeileC, 7)).AutoFilter Field:=4, _
Criteria1:="=" & Monat, Operator:=xlOr, Criteria2:="=" & MonatVergleich
'Gefiltertes kopieren
Range(.Cells(1, 1), .Cells(.UsedRange.Rows.Count, 7)).SpecialCells(xlCellTypeVisible).  _
_
Copy
'Einfügen
Sheets("Calcs2").Cells(1, 1).PasteSpecial
'Filter entfernen
Range(.Cells(1, 1), .Cells(LetzteZeileC, 7)).AutoFilter Field:=4
End With
End Sub
Ich bekomm jedoch bei einer größeren Datenmenge folgende Fehlermeldung:
Laufzeitfehler '1004':
MS Office Excel kann das Diagramm nicht erstellen, da der Datenbezug zu komplex ist. Probieren Sie die folgenden Tipps, um das Diagramm zu erstellen:
- Verwenden Sie Daten, die mit einem zusammenhängenden Rechteck markiert werden können.
- Verwenden Sie Daten von derselben Tabelle.
Zu dem dauert das Makro - wenns mal funktionier - beim einfügen zimlich lang.
Gib es dazu eine Lösung?
Vielen Dank.

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: AutoFilter - Datenbezug zu komplex!
23.06.2016 02:15:47
Daniel
Hi
es besteht grundsätzlich das Problem, dass Excel keine unterbrochenen Zellbereiche mag.
alles was lückenlos zusammenhängt, kann schnell verarbeitet werden, aber sobald lücken drin sind, vervielfacht sich der Aufwand.
Das Kopieren der Zeilen 1:1000 ist nicht viel aufwendiger als das Kopieren einer Zeile.
kopierst du jedoch die Zeilen 1 und 3, entsteht schon der doppelte Aufwand.
Sortiere daher mal vor dem Filtern die Tabelle nach der Filterspalte (4), so dass die Werte, die sichtbar bleiben und kopiert werden möglichst einen lückenlosen Zellblock bilden.
damit dürfte dann auch das Diagramm, welches offentsichtlich mit der Tabelle verknüpft ist, dann auch weniger Probleme haben.
Wenn nicht sortiert werden soll, könntest du die Meldung des Diagramms vielleicht dadurch vermeiden, dass du für das Diagramm einstellst, dass alle - auch ausgeblendete - Daten angezeigt werden.
Dann hat das Diagramm trotz Filtern seinen lückenlosen Zellbereich als Datenquelle.
Gruß Daniel

Anzeige
AW: AutoFilter - Datenbezug zu komplex!
23.06.2016 02:22:53
Noureddine
Hallo Daniel,
danke das du hilfst und vielen Dank für die Information - das habe ich mir gedacht, während ich einige Makros schon ausprobiert hatte.
Ich bin jetzt folgendermaßen vorgegangen, um mein Problem entgegen zu treten:
Kein Filter mehr!!!

Sub BestimmteZeilenEinfügen()
Dim Zeile As Long
Dim ZeileMax As Long
Dim n As Long
With Sheets("Calcs")
ZeileMax = .UsedRange.Rows.Count
n = 1
For Zeile = 2 To ZeileMax
If .Cells(Zeile, 4).Value = "201601" Or .Cells(Zeile, 4).Value = "201602" Then
.Rows(Zeile).Copy Destination:=Sheets("Tabelle2").Rows(n)
n = n + 1
End If
Next Zeile
End With
End Sub
Es ist nur so, das es auch ca. eine min. braucht bis das Makro durch ist.
Vielleicht kennst du eine schneller Methode?
Vielleicht mit der Find.Methode?

Anzeige
AW: AutoFilter - Datenbezug zu komplex!
23.06.2016 02:39:18
Daniel
Hi Noureddine
ich habe dir doch eine Methode genannt, wie du du dein Autofiltermakro beschleunigen kannst:
Vor dem filtern und kopieren: SORTIEREN !!!!!!!
ich wiederhole es nochmal für dich:
du musst erst SORTIEREN, dann filtern und dann kopieren.
beim SORTIEREN als Sortierkriterium die Spalte verwenden, nach der du filterst, dann stehen die kopierten Daten lückenlos und werden schneller kopiert.
wenn du nicht sortieren willst dann müsstest du so vorgehen
1. ALLE Zeilen kopieren
2. im Zielbereich mit einer Formel die Zeilen, die gelöscht werden sollen mit 0 kennzeichnen und die die stehenbleiben müssen mit der Zeilennummer:
=Wenn(oder(D2="201601";D2="201602");Zeile();0)
3. dann auf die ganze Tabelle die Menüfunktion DATEN-DATENTOOLS-DUPLIKATE ENTFERNEN anwenden mit der Hilfsspalte als Kriterium
naja, auch diese Methode beschreibe ich fast täglich hier im Forum, das hast du sicherlich schon mal gesehen wenn du hier aufmerksam mitliest.
Gruß Daniel

Anzeige
AW: AutoFilter - Datenbezug zu komplex!
23.06.2016 03:25:52
Noureddine
Hallo Daniel,
ich habe dich schon verstanden.
Diese Methode würde ich erst einsetzen, wenn ich keine andere finde.
Im laufe der Nacht, ist mir in den Sinn gekommen,das über meine Schleife abzuwickeln (siehe Code). Dann bräuchte ich nicht die zusätzlichen Anweisungen (Sortieren, Filtern....).
Das muss doch iregndwie auch mit einer Find-Methode gehen. Oder kann die Find-Methode nicht mit einem or Operator verknüpft werden.
Danke.

AW: AutoFilter - Datenbezug zu komplex!
23.06.2016 08:08:45
Daniel
Hi
Nein, bei .Find gibt es kein OR, mit welchem du nach zwei verschiedenen Suchbegriffen gleichzeitig suchen kannst. Du kannst die Jokerzeichen * und ? verwenden um ähnliche Begriffe zu suchen.
Hast du das mit dem Sortieren mal ausprobiert?
Dazu musst du deinen Code nicht ändern.
Du kannst die Tabelle auch von Hand nach der Filterspalte sortieren und dann dein Makro mit dem Autofilter starten um auszuprobieren, ob die Sortierung was bringt.
Wieviel Zeilen hat denn deine Tabelle und wieviele davon müssen kopiert werden (c.a)?
Gruß Daniel

Anzeige
AW: AutoFilter - Datenbezug zu komplex!
23.06.2016 14:34:51
Noureddine
Hallo Daniel,
ich hab dein Rat befolgt. Es klappt wenn ich die Daten sortiere, jedoch braucht die Anweisung ewig bein einfügen. Zudem muss ich Jahresvergleiche durchführen (201603 und 201503) und somit kann ich die Daten nicht zusammenführend Filtern.
Gibt es eine andere Lösung?
Vielen Dank nochmals.
Gesamtdaten: ca. 60.000 Zeilen
Kopiert werden ca. 8.000 - 10.000 Zeilen

AW: AutoFilter - Datenbezug zu komplex!
24.06.2016 19:44:47
Daniel
Hi
bei so Datenmengen muss man schon die Datei und die Aufgabenstellung gut kennen um schnelle Makros zu schreiben.
Meine Erfahrung ist halt, dass das Kopieren am schnellsten geht, wenn die zu kopierenden Zeilen einen oder zumindest möglichst wenige lückenlos zusammenhängende Blöcke bilden.
je weniger Blöcke es sind, um so schneller geht das kopieren, daher das sortieren.
in Sortierten Daten kann man einen Block eventuell auch so kopieren, dass man die erste infrage kommende Zeile sucht, mit .FIND und der Einstellung SearchDirection:=xlnext und die letze Zeile mit .FIND und gleichem Suchbegriff, aber mit der Einstelllung SearchDirection:=xlprevious, damit findet man dann die letzte Zeile mit dem Suchbegriff.
Somit hat man die erste und letzte Zeile eines Blocks und kann alle dazwischenliegenden Zeilen kopieren, in einem Schritt, dabei muss man nur 2x das .FIND anwenden.
wenn du zwei verschiedene Datumsstände kopieren willst, musst du das eben 2x machen.
Ansonsten sollte man auch, wenn man Formeln in der Datei hat die sich auf diese Zellen beziehen, für derartige Kopieraktionen die automatische Neuberechnung ausschalten. (Application.Calculation = xlcalculationManual). Damit wird's dann auch nochmal etwas schneller.
Gruß Daniel

Anzeige
AW: AutoFilter - Datenbezug zu komplex!
24.06.2016 21:48:42
Noureddine
Hallo Daniel,
vielen Dank für deine ausführliche Erklärung.
Ich bin heute mit dem Makro fertig geworden. Insgesamt dauert das Makro ca. 1:30 min.,
davon läuft ca. ne 0:30 min. die Filter-Kopier-Einfüg Anweisung.
Ich werde Montag noch das manuelle Kalkulieren zu Herzen nehmen, mit der Hoffnung so noch einige sek. zu sparen.
Letztendlich muss ich einsehen, das bei so großen Datenmengen Excel etwas länger braucht als wahrscheinlich z.B. Access. Ich find es nur Schade, das es keine anständige VBA-Methode/Lösung für solche Probleme gibt (wie z.B. die neu kenngelernte RemoveDuplicates-Methode).
Wenn ich dennoch zukünfigt dazu eine effektive Lösung finde, werde ich es hier veröffentlichen.
Nochmals vielen Dank für deine/euere Hilfe.

Anzeige
AW: AutoFilter - Datenbezug zu komplex!
24.06.2016 22:17:12
Daniel
Hi
zeig doch mal deinen code.
und mit 60.0000 Zeilen ein Diagramm zu befüllen ist auch nicht unbedingt sinnvoll.
ein normaler Bildschirm hat nur c.a. 1000-2000 Pixel
Gruß Daniel

Links zu Excel-Dialogen

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige