Office: (Office 2016) Größere Datenmengen effektiv Filtern und sichtbare Zeile kopieren

Hallo Zusammen,

ich komme dank eurer Hilfe gut voran.

Leider wird meine "Datenbank" mittlerweile so groß, dass mit Autofilter gefilterte Daten mehrere Sekunden benötigen, bis diese auf ein anderes Tabellenblatt kopiert sind.

Ursprünglich habe ich die sichtbaren Zeilen mit einer For-Next-Schleife auf "hidden = false" überprüft und dann alle Zeilen auf die das zutraf kopiert. Das dauert aber mittlerweile zu lang. Hab dann versucht mit "Selection.SpecialCells(xlCellTypeVisible).Copy" das ganze zu beschleunigen - leider ohne großen Erfolg.

Gibt es eine effektivere Methode das Ganze zu beschleunigen? Evtl. einen komplett neuen Ansatz (auch ohne Autofilter)?

Vielen Dank für eure Hilfe!

VG
Chris

 

Hi Chris,

hast du das Kopieren schon mal auf diesem Weg versucht:

Code:

Dim rngBereich As Range
With ActiveSheet.AutoFilter
    Set rngBereich = .Range.SpecialCells(xlCellTypeVisible)
    rngBereich.Copy Worksheets("Tabelle2").Range("A1")
End With

Eventuell kannst du noch anschließend bei Bedarf die 1. kopierte Zeile (welches die Überschriftenzeile des Autofilterbereichs ist) löschen.

Bis später,
Karin

 

Hallo Karin,

danke für Deinen Tip. "xlCellTypeVisible" hatte ich schon probiert. Es scheint keinerlei Geschwindigkeitsvorteile zu bieten.

Mittlerweile ist mir aufgefallen, dass die Autofilterfunktion sehr langsam ist. Gibt es hierfür evtl. eine anderen Lösung?

VG
Chris

 

Hi Chris,

dann laufe ohne Filter in einer Schleife über alle Zeilen und prüfe, ob in der Spalte, nach der du normalerweise filtern würdest, die betreffende Bedingung zutrifft und kopiere dann diese Zeile.

Bis später,
Karin

 

Hallo Karin,

ich habe es jetzt nochmal mit diesem Code von Dir probiert. Einmal hat es funktioniert, aber jetzt bekomme ich immer einen "Laufzeitfehler 91: Objektvariable oder With-Blockvariable nicht festgelegt". Der Fehler tritt mit der Zeile "Set rngBereich = ….." auf.
Ich habe nichts verändert und suche seit Stunden (mittlerweile verzweifelt) den Fehler.

Code:

Dim rngBereich As Range
With ActiveSheet.AutoFilter
    Set rngBereich = .Range.SpecialCells(xlCellTypeVisible)
    rngBereich.Copy Worksheets("Tabelle2").Range("A1")
End With

Grüße
Chris

 

Hi Chris,

in dem Fall gibt es in deiner Tabelle keinen Autofilterbereich.

Bis später,
Karin

 

Hallo Karin,

irgendwie hat mich der Autofilter und seine "Langsamkeit" jetzt so genervt, dass ich meine Tabelle nach Access verschoben habe und Excel nur noch als Frontend benutze. Mit ADODB bekomme ich die Abfragen mittlerweile ganz gut hin. Schreiben und synchronisieren kann ich noch nicht....und bin auch noch auf der Suche nach einem guten Tutorial.
Aber allein schon die Geschwindigkeit ist super :)

Hat jemand von euch einen Link für ein (gutes) ADO Toturial indem erklärt wird, wie ich von der Userfom in Excel direkt in Access schreibe bzw. Access mit Excel synchron halte?

Vielen Dank für eure Hilfe!

Grüße
Chris

 

Für Alle die vernünftig Englisch verstehen:
https://www.youtube.com/watch?v=p_IhfObVc6A&list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5&index=48&t=0s

 

Hi Chris,

ich verstehe nicht, weshalb du so auf dem Autofilter herumreitest, es gibt doch andere, schnellere Möglichkeiten - Schleife über alle Zeilen wie bereits geschrieben, auch mit Arrays kann man arbeiten, ohne dass man in der Schleife Datensatz für Datensatz einzeln kopiert...

Bis später,
Karin

 

Hallo Karin,

na was heißt auf dem "Autofilter herumreitest"? Es hat sich für mich halt einfach herausgestellt, dass dieser bei mehr Datensätzen recht langsam wird. Ich habe eine Möglichkeit gesucht in der ich gefilterte Daten innerhalb ein oder 2 zehntel Sekunden in eine Listbox übertragen kann. Leider haben alle von mir versuchten Möglichkeiten versagt.
Deshalb mein Ausweg mit der Access DB. Diese hat bei größeren Datenmengen ja schliesslich auch noch weitere Vorteile.
Die von Dir beschriebene Schleife wird ab einem bestimmten Datensatzbestand auch sehr langsam. Mit Arrays im großen Stil bin ich nicht so vertraut - deshalb auch nicht probiert.

Ich wollte Dir nicht auf den Schlips treten. Habe schon viele hilfreiche Tips von Dir und anderen bekommen. Aber gerade mit Blick auf zukünftige Datenmengen war Access jetzt einfach das Richtige. Und von Access ist dann ein Umstieg auf SQL recht einfach.

VG
Chris

 

Hi Chris,

wenn du dein Problem anders gelöst hast und damit zufrieden bist ist das doch völlig in Ordnung. Ich habe mich auch nicht auf den Schlips getreten gefühlt, ich bin halt nur der Meinung (und habe dazu auch anderweitig Tests gelesen), dass man nicht alles mit Autofilter lösen sollte, da er bei einer größeren Datenmenge eben wesentlich langsamer ist als eine ganz normale Schleife über die Datensätze mit Prüfung der betreffenden Bedingung(en) - nur darauf wollte ich hinweisen.

Bis später,
Karin

 

Hallo Karin,

dann is ja gut :))

Nur so zum Verständnis.
Es kommen jeden Tag ca. 220 Datensätze hinzu. Jeder Datensatz enthält rund 50 verschiedene Daten. Das macht bei 5 Werktagen ca. 1000 Datensätze mal 50 Einzeldaten. Nachdem ich erst bei 1500 Datensätzen war und schon Performanceprobleme hatte, war das wahrscheinlich der richtige Schritt. Ich hoffe Du kannst mir zustimmen.
Jetzt bin ich nach oben offen und kann auch recht einfach auf SQL umsteigen falls dies irgendwann nötig sein sollte.
Da geplant ist, dass sich das Datenvolumen bei erfolgreicher Einführung fast verzehnfachen wird, muss ich mir keine großen Sorgen um die Zukunft machen .... das hoffe ich zumindest....

VG
Chris

 

Hi Chris,

Excel ist sowieso nicht dazu gedacht, als Datenbank zu fungieren - nur wird es halt oft dazu "missbraucht". Bei kleineren Datenmengen mag das durchaus funktionieren, aber es macht eben Probleme, wenn die Datenmengen ein bestimmtes Maß übersteigen. Als Datenbankprogramm ist Access schon das richtige, da es eben auf größere Datenmengen ausgelegt ist. Da ich (vor Jahren) auch ein wenig Access programmiert habe kann ich dir nur dazu gratulieren, dass du bei deinen großen Datenmengen darauf umgestiegen bist.

Bis später,
Karin