Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1968to1972
1968to1972
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query

Forumthread: Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query

Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query
02.04.2024 09:48:10
MaxBlack
Guten Tag liebe Community,

Ich habe Ende letzten Jahres zwei Excel-Tabellen via Power Query verbunden.
Nun wurde Anfang März ein Tabellenblatt zum auswerten hinzugefügt. Bis letzte Woche funktionierte auch alles hervorragend.

Beim Aktualisieren kommt die Fehlermeldung "Von der Abfrage wurden mehr Daten zurückgegeben, als auf ein Arbeitsblatt passen".
Ich habe mit Hilfe der Tastenkombi "Strg+Ende" festgestellt, dass ein Arbeitsblatt der Quelldatei bis zur letzten Zeile (1.048.576) "genutzt" wird.
Ich habe schon probiert einige hundert Zeilen zu markieren und den Inhalt zu löschen, leider ohne Erfolg.

Ich habe versucht die "fehlerhafte" Tabelle zu kopieren, dabei sind folgende "Elemente" aufgetaucht: "Fotos" und "OS_7". es kann sich keiner hier erklären, was das sein könnte.

Ich habe jetzt bei Power Query eine max. Anzahl an Zeilen festgelegt.
Ich könnte auch die Quelldatei funktionsfähig machen, in dem ich eine andere Tabelle kopiere und die Daten dort einfüge, aber ich will das Problem gerne verstehen und hoffentlich auch so lösen.

Kurz gesagt: Warum nutzt Excel in einem Tabellenblatt die maximale Anzahl an Zeilen?

Grüße Max


Anzeige

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query
02.04.2024 10:28:37
ralf_b
"Warum nutzt Excel in einem Tabellenblatt die maximale Anzahl an Zeilen?"
weil es muß. Excel ist sozusagen dumm. Der Nutzer muß die Beschränkungen des Programmes berücksichtigen. Wenn du mehr als die max Zeilenzahl benötigst, dann ändere etwas. Möglicherweise sogar das Programm was du verwendest.
Anzeige
AW: Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query
02.04.2024 10:45:19
MaxBlack
Danke für die Antwort.
Ein anderes Programm steht leider nicht zu Debatte.

"weil es muss" ist unbefriedigend. Ich habe Excel ja gesagt es soll die Zellen der von ca 600 Zeilen löschen. Warum gibt er mir dennoch aus, dass dort etwas drin sein soll...

Praktischerweise benötige ich insgesamt ca 28800 Zeilen. Zusammengesetzt aus 28 Tabellenblättern und max. 1100 Zeilen pro Blatt. Lediglich dieses eine Tabellenblatt macht Ärger.
Anzeige
AW: Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query
02.04.2024 15:04:01
ralf_b
werden die 1 Million Zeilen denn benutzt oder sagt Excel nur das die ganze Zeilenanzahl belegt ist?
AW: Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query
02.04.2024 15:10:53
MaxBlack
Es sind nur in den ersten 50 Zeilen Daten eingetragen. Ich habe spaßenshalber sogar das gesamte Arbeitsblatt leer gemacht. (oben-links alle Zellen markiert und Zellen löschen).
Mit der Tastenkombi Strg+Ende lande ich imemrnoch auf dem selben Feld in der untersten Zeile des Blattes.
Anzeige
AW: Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query
02.04.2024 15:35:02
ralf_b
es gibt eine Möglichkeit das zu reparieren.
Dazu ist es notwendig den VBA Editor zu öffnen und dort im Direktfenster diverse Befehle auszuführen.

Siehe Hier https://newbedev.com/deleting-rows-in-excel-doesn-t-reset-last-cell-used-range
Zusammenfassung:
Wenn Usedrange.address nur ganze Spalten anzeigt z.b. $A:$I und ?activesheet.usedrange.rowheight im Direktfenster Null ergibt,
dann wird mittels setzen der Rowheight die Usedrange quasi repariert.

Befehl im Direktfenster(aufrufen mit STRG +"G"): activesheet.usedrange.rowheight = 23

mit dieser Zeile die Änderung prüfen
?activesheet.usedrange.address()
vorher $A:$I
nachher z.b. $A$1:$I$1034488

jetzt kann man die Überflüssigen Zeilen einfach löschen.
activesheet.rows("20:1034488").delete

Es könnte auch speichern, schliessen und Datei neu öffnen ausreichen

gruß
rb
Anzeige
AW: Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query
02.04.2024 15:04:50
Yal
Hallo Max,

eine der Quelltabellen hat wohl eine Einstellung, die dazu führt, dass unnötige Zeilen gelesen und verarbeitet werden.
Du musst zuerst isolieren, welches Blatt/Tabelle der Verursacher ist. Dann kannst Du eine Lösung aufbauen, die unnötige Zeilen so früh wie möglich auszusortieren, und die Methode auf allen Blättern/Tabellen anwenden.

Wenn nur 28.800 Zeilen erwartet sind, gibt es kein Grund sich mit "600 unter max" Zeilenzahl zufrieden zu geben.

VG
Yal
Anzeige
AW: Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query
02.04.2024 15:14:04
MaxBlack
Welches Blatt das "Problemblatt" ist steht fest. Ich habe auch den Hammer rausgeholt und alles einmal gelöscht (Formatierung, Formeln etc.) dennoch meint Excel in der letzten Zeile steht noch etwas...
AW: Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query
02.04.2024 15:19:06
Yal
Sehr gut.

Dann kannst Du in Power Query eine Methode verwenden, um aus diese angeblich 1.048. ... Zeilen die nur x relevante Zeilen zu isolieren.

Die Quelle mag das Problem sein, die Lösung ist aber in der Art, wie Du die Quelle mit PQ behandelst, zu suchen (bzw. finden).

VG
Yal
Anzeige
;

Forumthreads zu verwandten Themen

Summe bilden und Zeilen löschen
Holger

Guten Tag, ich bitte um Hilfe.
In Spalte A stehen Nummern, eine Nummer kann einmal oder auch mehrmals vorkommen. In Spalte E stehen die dazugehörigen Summen. Ich möchte realisieren, dass bei doppelt oder mehr gleicher Nummern, die Summe dieser gleichen, der ersten dieser Nr. zugeschrieben w...
17.03.2011 13:07:18
Thread-ID: 1206098
5 Beiträge
Maximale Zeilenanzahl pro Tabellenblatt
MR

Liebe Spezialisten,gibt es eine Möglichkeit die maximale Zeilenanzahl von 65000 Zeilen pro Tabellenblatt zu erhöhen. Ich muß ca.78000 Datensätze pivotmäßig auswerten und die Konsolidierung bringt mir nicht die gewünschten Ergebnisse.Gruß und Danke Michael
21.05.2002 10:35:27
Thread-ID: 122344
3 Beiträge
Pivot-Tabelle über mehr als 1.048.576 Zeilen
Peter

Hallo zusammen,
ich habe Datensätze über mehr als 1.048.576 Zeilen; diese habe ich auf 2 Tabellenblätter aufgeteilt. Ist es möglich, über beide Tabellenblätter eine einzige Pivot-Tabelle zu ziehen? Wenn ja, wie? Gerne auch per VBA. Bin für Tipps dankbar!
Beste Grüße,
Peter
PS: H...
30.06.2011 15:50:51
Thread-ID: 1220359
3 Beiträge
Anzeige
BITTE Hilfe bei Makro! Bestimmte Zeilen löschen
Micha

Hallo liebe Spezialisten.
Ich suche ein Makro, welches mir nur die Zeilen mit dem Start und Enddatum anzeigt.
Alle übrigen Spalten sollen gelöscht werden.
Eine Beispieldatei befindet sich im Dateianhang.
Beschreibung befindet sich im Kommentar A2
https://www.herber.de/bbs/us...
02.05.2012 16:07:31
Thread-ID: 1261865
10 Beiträge
Farbige Zeilen nicht löschen
Andi

Hi Leute, ich werd grad wahnsinnig - ich habe eine Tabelle, in der leider nur ca. jede 5. Zeile einen Wert enthält (Buchstaben)
Ich habe die Werte mit bedingter Formatierung eingefärbt und wollte jetzt einfach die leeren Zeilen dazwischen entweder löschen oder ausblenden, damit ich eine du...
26.04.2013 18:38:26
Thread-ID: 1310690
5 Beiträge
VBA Anzahl ohne Duplikate inkl. Filtern
Verena

Hallo,
ich habe folgendes Problem: ich habe eine Tabelle mit vielen Daten. Ich möchte die Anzahl der numerischen Einträge einer Spalte zählen jedoch ohne die Duplikate. Allerdings muss ich vorher auch filtern. Ich weiß nicht so recht wie ich das alles kombinieren muss/ kann.
Also beispi...
24.08.2013 19:17:58
Thread-ID: 1328242
4 Beiträge
Anzahl ohne Duplikate gekoppelt mit Wenn-Funktion
Sonja

Hallo,
ich möchte in einer (gefühlt ewig langen) Excel-Tabelle folgendes berechnen:
- Anzahl von Kunden (ohne Duplikate)
- die im Jahr X eingekauft haben
Beispieltabelle:
A B
|A|2010|
|A|2010|
|B|2010|
|C|2010|
|A|2011|
|A|2012|
|B|2012|
Fra...
30.07.2014 17:33:03
Thread-ID: 1373223
2 Beiträge
Anzahl ohne Duplikate, leere Zellen ignorieren
Bonduca01

Hallo,
Der Code unten gibt mir in Tabelle 1 Zelle (3,8) die Anzahl der Zellen ohne Duplikate aus.
Leider zählt der Code auch die leeren Zellen mit.
Gibt es eine Möglichkeit, dass er die leeren Zellen nicht zählt? Sub TT() Worksheets("Tabelle1").Activate Cells(3, 8) = Anzahl End Su...
14.07.2015 10:39:10
Thread-ID: 1436244
4 Beiträge
Wieviele Zeilen möglich bei Spaltensortierung
Dieter(Drummer)

Guten Tag Spetialisten,
wieviel Zeilen können maximal, bei Sortierung mit VBA, von 2 Spalten genutzt werden?
Gibt es da eine Zeilen-Anzahlgrenze?
Mit der Bitte um Info, grüßt
Dieter(Drummer)
11.08.2017 15:33:34
Thread-ID: 1574006
7 Beiträge
Anzeige
Wie viele Zeilen sind in Tabellenblatt ausgeblende
Nicole

Hallo zusammen,
gibt es einen Code, der mir ausgiebt, wie viele Zeilen in einem Tabellenblatt durch einen Autofilter oder ähnliches ausgeblendet sind?
Vielen Dank schonmal
Gruß Nicole
14.01.2018 13:58:48
Thread-ID: 1602177
2 Beiträge
Anzahl ohne Duplikate wenn 2 Bedingungen gegeben
Sarah

Hallo,
meine Datentabelle ist folgendermaßen aufgebaut:
Spalte A: Monat (Januar-Dezember)
Spalte L: Name des Supermarkts
Spalte J: Vorgangsnummer
Q7: April (als Beispiel)
T2: Aldi (als Beispiel)
R3: Zahl der letzten Zeile, die in den Bereich integriert werden soll
06.09.2018 13:31:56
Thread-ID: 1643783
3 Beiträge
max. anzahl zeilen ermitteln
jan

ich möchte von der tabelle VEREINE aus die max. anzahl der zeilen in der spalte a der tabelle SUCHMASCHINE ermitteln. nachfolgenden code verwende ich:Function FindMaxRowsSearchmaschine() As IntegerDim NameOfTargetTable As String
Dim NameOfActivTable As StringNameOfActivTable = "Vereine"
N...
06.01.2003 15:19:57
Thread-ID: 199519
5 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Max Anzahl Zeilen ohne Inhalt in Verbindung mit Power Query

Schritt-für-Schritt-Anleitung

Um das Problem der maximalen Zeilenanzahl in Excel zu beheben, gehe wie folgt vor:

  1. Identifiziere das Problemblatt: Bestimme, welches Tabellenblatt das Problem verursacht. Du kannst dies tun, indem du die Daten in jedem Blatt überprüfst.
  2. Leere alle Zellen: Markiere alle Zellen (Strg + A) und lösche den Inhalt sowie die Formatierungen. Dies kann manchmal nicht alle Daten entfernen.
  3. Verwende Power Query: Gehe zu Power Query und filtere die Daten, um nur die relevanten Zeilen zu behalten.
  4. Öffne den VBA-Editor: Drücke Alt + F11, um den VBA-Editor zu öffnen.
  5. Gib den Befehl ein: Im Direktfenster (Strg + G) gebe folgenden Befehl ein, um die Used Range zu reparieren: 
    ActiveSheet.UsedRange.RowHeight = 23
  6. Überprüfe die Used Range: Überprüfe mit 
    ?ActiveSheet.UsedRange.Address

    ob die Adresse nun korrekt angezeigt wird.

  7. Lösche überflüssige Zeilen: Benutze den Befehl: 
    ActiveSheet.Rows("20:1048576").Delete

    um die überflüssigen Zeilen zu entfernen.

Häufige Fehler und Lösungen

  • Excel zeigt die maximale Anzahl an Zeilen an: Wenn Excel die maximale Anzahl von 1.048.576 Zeilen anzeigt, bedeutet dies oft, dass Excel denkt, dass diese Zeilen belegt sind. Überprüfe die verwendeten Bereiche und führe die Schritte zur Bereinigung durch.

  • Leere Zeilen werden nicht entfernt: Manchmal bleiben Formatierungen oder versteckte Daten in Zellen zurück. Stelle sicher, dass du auch die Formate entfernst.

Alternative Methoden

  • Power Query: Nutze Power Query, um leere Zeilen zu entfernen. Wähle Daten -> Abfragen und Verbindungen, um deine Abfragen zu bearbeiten und unerwünschte Zeilen zu filtern.

  • VBA-Skripte: Wenn du oft mit großen Datenmengen arbeitest, könnte es sinnvoll sein, ein VBA-Skript zu erstellen, das das Löschen und Bereinigen von Daten automatisiert.

Praktische Beispiele

  1. Leere Zeilen entfernen mit Power Query:

    • Lade deine Daten in Power Query.
    • Wähle die Spalte aus, die du bereinigen möchtest.
    • Klicke auf Start -> Zeilen entfernen -> Leere Zeilen.

  2. Verwenden von VBA zur Bereinigung:

    Sub ClearUnusedCells()
   Cells.Clear
   ActiveSheet.UsedRange.RowHeight = 23
End Sub

Tipps für Profis

  • Datenanalyse: Wenn du große Datenmengen analysierst, beachte, dass Excel eine maximale Zeilenanzahl hat. Plane entsprechend, um die Daten in kleinere Segmente zu unterteilen.

  • Power BI: Wenn du oft mit mehr als 1 Mio. Zeilen arbeitest, könnte Power BI eine bessere Lösung sein, da es leistungsfähiger ist, wenn es um große Datensätze geht.

FAQ: Häufige Fragen

1. Wie viele Zeilen hat Excel maximal?
Excel hat eine maximale Anzahl von 1.048.576 Zeilen pro Arbeitsblatt.

2. Warum kann ich keine Zeilen in Excel löschen?
In manchen Fällen bleiben Formatierungen oder unsichtbare Daten in den Zellen, die das Löschen verhindern. Nutze die oben genannten Schritte, um dies zu beheben.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige