Excel Filter 10000 Limit? Lösung?
22.05.2022 17:17:19
Daniel
Hi Leute,
ich habe 120000 Einträge, und davon mit Filter bleiben 25000 Einheiten übrig. Wenn ich die gefilterten 25000 Werte nun versuche zu kopieren, dann weigert sich Excel mit dem Hinweis, dass "der Datenbezug zu komplex ist". Das hängt wohl mit den maximal 10000 Werten die der Filter abkann zusammen.
Er kopiert die Werte zwar aber eben nicht gefiltert sondern mit allen Werten (also statt 25000 bleiben beim Einfügen woanders dann wieder 120000 übrig).
95000 Zellen davon sind Leerzeilen/Zellen. Ich könnte höchstens die ganze Spalte kopieren und in eine weitere Spalte einfügen und Excel dann dort in dieser kopierten Spalte die leeren Zellen herauslöschen lassen via Makro, dann sind es zwar immer noch 25000 die übrig bleiben aber die kann man ja dann kopieren, da kein Filter mehr benötigt wird. Da 95000 Leerzellen nicht mal eben in 3 Sekunden gelöscht sind, wollte ich nochmal nach einer eleganteren Lösung fragen...
hat jemand Rat?
Moin!
>>Da 95000 Leerzellen nicht mal eben in 3 Sekunden gelöscht sind<<
Spalte A ab A2 20.000 Werte und 100.000 Leerzellen.
Hilfsspalte B
B1: 0
B2:B120001 =WENN(A2="";0;ZEILE())
Spalte B kopieren und als Wert einfügen
Auf Spalte B Duplikate entfernen durchführen.
Dauert auf meiner 7 Jahre alten Möhre 0,344 Sekunden!
Sub Quickie()
Dim Start#
Range("B1") = 0
With Range("B2:B120001")
.Formula = "=If(A2="""",0,Row())"
.Value = .Value
End With
Start = Timer
Range("A1").CurrentRegion.RemoveDuplicates 2
Debug.Print Format(Timer - Start, "0.000 sec")
End Sub
Hi Ralf,
au weia, die Funktion kannte ich noch gar nicht bzw. habe das noch nie benutzt ("Duplikate entfernen"). Ich arbeite mit Excel seit 20 Jahren, lol. Vielen Dank!! Das ist ja super!!
Hallo,
wenn es wirklich Leerzeilen sind probier mal den Einzeiler:
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Hallo Matthias!
Das funktioniert zwar, dauert bei meiner Beispieldatei aber 187,012 sec
Dies ist dann schlappe 543mal langsamer als die RemoveDuplicates-Methode!
Gruß Ralf
Ich habe jetzt mal diverse Beschleunigungen probiert
Berechnung manuell
Screenupdating ausgeschaltet
Bereich verkleinert
Ergebnis: "Nur noch" 132 Sekunden (statt der 0,3 Sekunden beim Duplikate entfernen).
Wer es nachstellen will:
Konstruktionsmakro:
Sub Konstruktion()
Range("A1") = "SpalteA"
With Range("A2:A120001")
.Formula = "=If(Mod(Row(),6)=0,Row(),0)"
.Value = .Value
End With
End Sub
Strg+h
Suchen nach: 0
Ersetzen durch: nichts
Alle ersetzen
Mein SpecialCells-Makro:
Sub SpecCells()
Dim Start#
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Start = Timer
Range("A2:A120001").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Debug.Print Format(Timer - Start, "0.000 sec")
End Sub
Ja, perfekt. So in etwa wollte ich das auch hinkriegen - sofern mit entirerow.delete. Aber Deine erste Variante ist auch deswegen besser, da nur eine Spalte betroffen ist und nicht die entirerow gelöscht wird.
Leute wie Du erleichtern einem das Leben. Das ist wirklich viel wert, danke Dir
Hi Mathias,
ja, danke, genau das Zeile für das Makro hatte ich vorher gesucht. Ich gucke mal was ich am ende nutze. Denke die Idee von Ralf ist noch ein bisschen schneller.
Schönen Sonntag Dir
Hi
Die 10.000 Werte, die im Autofilter angezeigt werden, haben damit nichts zu tun.
Das Problem ist nicht die Anzahl der Zeilen, sondern die Verteilung der zu kopieren den Zeilen.
Der für Excel kritische Wert ist die Anzahl der lückenlos zusammenhängenden Blöcke, die kopiert oder gelöscht werden sollen.
Willst du beispielsweise die Zeilen 1 und 3 kopieren oder löschen, dann ist das für Excel doppelt so "anstrengend" wie das löschen aller Zeilen von 1 bis 10.000, weil es eben zwei Blöcke sind und nicht einer. Die Größe dieser Blöcke ist egal, wichtig ist die Anzahl.
Dh deine Kopier- oder Löschaktion funktioniert problemlos und sehr schnell mit beliebigen Datenmengen, wenn du vor dem Filtern die Liste so sortiertst, dass die gefilterten Zeilen einen bzw so wenig wie möglich lückenlos zusammenhängenden Blöcke bilden.
Der Grün hierfür ist, das Excel beim Löschen von Zeilen im Hintergrund alle geöffneten Exceldateien nach Formeln durchsucht, die Bezüge auf die gelöschten Zellen enthalten um die Formeln dann entsprechend anzupassen. (Der Bezug auf A1:A10 wird zu A1:A9, wenn du Zeile 7 löschst)
Das Problem ist das Excel dies für jeden gelöschten Block erneut durchführen muss. Je weniger Blöcke du hast, um so schneller gehts.
Wenn man nicht sortieren kann oder will, verwendet man zum löschen von Zellen mit Bedingung wie von den anderen gezeigt das Duplikate entfernen. (Was übrigens auch von Hand ohne Makro verwendbar ist)
Dieses ist beim Löschen deswegen so schnell, weil es diese Hintergrundfunktion (Anpassen der Formeln) des normalen Löschens nicht ausführt und damit viel Aufwand spart.
Gruß Daniel