Office: (Office 2013) Verwendeter Bereich lässt sich nicht anpassen

Hallo,

ich habe schon viele Beiträge zu dem Thema gelesen. Meistens hatten die Betroffenen aber eine ältere Excel Version und waren schnell mit "einfachen Lösungen" bedient.

Ich bin am verzweifeln.
in meiner Arbeitsmappe befinden sich viele Tabellenblätter (ca. 20) und die Arbeitsmappe wächst immer weiter und in der letzten Zeit zu schnell. Seit heute weiß ich warum. Es liegt am verwendeten Bereich von Excel.

Beispiel:
Auf einem Tabellenblatt sind 50 Zeilen mit Daten gefüllt. Verwende ich die Funktion Strg+Ende wird mir die letzte Zelle des Verwendeten Bereichs angezeigt (L1048576).
Ich habe die Zeilen 51 - 1048576 komplett gelöscht:
Cursor auf Zeile 51 -> Strg+Umschalt+Pfeil nach unten -> Strg+G -> Inhalte.. -> Zellen als Leerzellen formatiert -> Start -> Bearbeiten -> Alles löschen -> Speichern -> Schließen


Das ist der Weg, der die Probleme aller anderen Beitragssteller beseitigt hat. Aber nicht meine :-D.
Das Interessante ist, dass dieser Weg bei dem Löschen der leeren Spalten (Von L- XFD) funktioniert aber nicht bei den Leeren Zeilen

Danach habe ich auf dem Tabellenblatt folgenden Code eingefügt, der das ebenfalls beheben sollte:
Code:

Mit STRG + ENDE wirft mir aber immer wieder L1048576 aus.
Das Abspeichern in anderen Formaten half ebenfalls nicht.

Auch das Abspeichern in diversen anderen Formaten half nicht.
Ebenso wie das Starten an einem anderen Rechner.

Ich hoffe irgendwo findet sich jemand, der mir da doch helfen kann.

Ein VBA Code, der das beheben kann würde mir auch sehr weiter helfen.

Eine Beispieldatei habe ich im Anhang abgelegt. Probiert es selbst ;-).

Liebe Grüße

:)

 

Hallo,

lange Zeit hatte auch ich immer wieder feststellen können, dass einzelne Tabellen in unterschiedlichsten Mappen auf einmal 1048576 Zeilen hatten.

Sehr lange hat es gedauert, bis ich letzten Sommer zufällig einen Grund für die scheinbar willkürliche Erweiterung von Tabellen gefunden habe.

Immer wenn aus einer gefilterten Tabelle ganze Spalten markiert und an anderer Stelle Eingefügt werden, expandiert die Tabelle bis zum Ende.

Bei ungefilterten Tabellen werden beim kopieren ganzer Spalten nur so viele Zeilen erzeugt, wie in der Ausgangstabelle vorhanden sind.

Eventuell gibt es noch weitere Möglichkeiten, wann dieses passiert, die habe ich aber noch nicht herausgefunden.

Und gerade jetzt habe ich herausfinden können, dass gefilterte Zeilen auch für die Expansion einer Tabelle bis zur letzten bzw. vorletzten Spalte verantwortlich sind.

Dazu habe ich in der Tabelle mit der gefilterten Zeile zusätzlich eine Spalte ausgeblendet. Danach die durch das filtern übrig gebliebenen Zeilen in die Zwischenablage übernommen und in eine andere Tabelle eingefügt.

Mit Strg+Ende wird mir nun die Spalte XFC als letzte angezeigt.

Vielleicht helfen dir diese Infos etwas weiter.

Übrigens wird dir nach dem Löschen von Zeilen und oder Spalten das korrekte Ende erst nach dem sichern, schließen und erneuten öffnen angezeigt.

 

Hallo danke für den Ansatz,

Ich habe auf diesem Tabellenblatt keine Filter gesetzt.
Der Bereich ist auch nicht auf allen Tabellenblättern bis 1048576 gerutscht.

Auch nach erneutem öffnen ist der Bereich nicht angepasst worden.

Mir geht es einfach darum, dass ich es beheben möchte.

 

Evtl. wurden mal die Zahlenformate spaltenweise angegeben. Kann ja auch versehentlich passieren.
Am Scrollbalken auf der rechten Seite ist ja schon ersichtlich, dass die Tabelle weit nach unten reicht.

Wenn es sich nicht um mehr, als die paar Zeilen handelt, kann man die Zellen, die wirklich den "usedrange" darstellen, markieren und mit allem SchnickSchnack in ein neues Blatt kopieren.
Dann ist alles wieder schick.

 

leider geht das nicht so einfach. Das hier gepostete Tabellenblatt ist nur ein Beispiel. Es ist nur eins von ca. 20 Tabellenblättern die alle mit Makros und Formeln verzweigt sind. Deshalb kann ich das nicht so einfach machen.

 

Hallo,

also wenn die überschüssigen Leerzeilen von dir gelöscht wurden und du hast die Mappe gesichert, dann müssen diese auch weg sein.

Es kann aber sein, dass zum Zeitpunkt öffnen, schließen, oder sichern, ein Makro abläuft, das dafür sorgt, dass wieder die Leerzeilen bis zum Ende eingefügt werden.

Am einfachsten kannst du dies wohl überprüfen, indem du die Mappe einmal als normale Excel Arbeitsmappe ohne Makros (.xlsx) speicherst, die überschüssigen Zeilen löschst, sicherst, schließt und erneut öffnest. Ist die Mappe danach auf Normalmaß geschrumpft, dann musst du prüfen, was deine Makros so anstellen.

 

Das wird auch nichts bringen.
Der usedrange ist per Definition ein Bereich, der mal benutzt worden ist.
Auch wenn zB Formate gelöscht worden sind, ändert das nichts am usedrange.

Auch mit VBA kann man nicht anders oder besser löschen.
Es bleibt nur, meines Erachtens nach, den gewünschten Bereich in ein neues Blatt zu kopieren. Selbst, wenn es 30 Sheets sind, dauert das nur Minuten und man hat Ruhe.

... oder man hat halt Pech gehabt. Aus Fehlern lernt man ja auch.

 

Hallo Andrea?

Da irrst du. UsedRange ist der gerade, jetzt, eben verwendete Bereich. Der früher, irgendwann mal verwendete, kannst du nicht mehr in Erfahrung bringen, er ist verloren nach dem Speichern.

ich habe andere Erfahrungen gemacht. Siehe: http://www.ms-office-forum.net/forum...75#post1781375

 

Er hat die Mappe ja zur Verfügung gestellt.
Wenn du mir zeigen kannst, wie man das flickt, wäre ich dir wirklich dankbar.

Man soll... ich sage bewusst soll.... über Application.ActiveSheet.usedrange einen Reset erzwingen können. Das könnte ich aber nicht erreichen.
Wenn ein ZellFormat mal gesetzt war und gelöscht wird, wird eben dieser Range per Shift Umschalt Leertaste einbezogen. Und das ist ja der usedrange. Auch nach einem cells.clear

Zumindest bei mir unter XL 2013 + 2016

 

Hallo,

es ist mir ebenso mit meinen bekannten Mitteln nicht gelungen, UsedRange zu resetten. Offenbar Ist deine Datei "kaputt", oder was auch immer.
Ich hatte noch vermutet, dass es an den fetten Rahmen liegt. Da UsedRange bei diesen fettesten Rahmen immer die nächste eigtl. unbeschriebene Zelle mitzählt (weswegen ich diesen nun nicht mehr benutze).

Eine kurze Recherche hatte jedoch Erfolg.
Schau mal hier: Locate and reset the last cell on a worksheet

Das Add-In funktioniert!

Bitte um Rückmeldung, wer das Add-In analysiert hat, woran es tatsächlich lag. Es ist nicht geschützt.

 

Danke für den Tipps mit dem Add In,

leider jagt bei mir aber ein Problem das nächste ...
Ich habe das Add in "Exess Format Cleaner 1.3" erfolgreich gedownloaded, implementiert und aktivert.
Unter Optionen habe ich im Menüband "Add In" aktiviert.
Aber aus einem unerklärlichen Grund wird die Registerkarte "Add In" nicht angezeigt und somit kann ich dieses nicht ausführen.

Glücklicherweise bin ich durch die "Add In"-Idee auf eine Weitere Lösung gestoßen.
Ich habe die Registerkarte Inquire aktiviert. Unter "Verschiedenes" befindet sich das Feld "Übermäßige Zellformatierung entfernen". Diese Anwendung passt den genutzten Bereich richtig an :-). Einziger kleiner Haken. Die Zellhöhe muss dafür identisch sein.

Trotzdem würde mich interessieren wie ich die Registrierkarte "Add In" aktivieren kann.

 

Hallo,

Als ich das Add-In installiert hatte, war die Add-In Leiste sofort verfügbar, wie üblich.
Als ich das Excel-Fenster schloss und wieder öffnete war es weg, obwohl der Haken bei dem Add-In noch drin war. Hab den Haken rausgemacht, geschlossen, wieder rein, schon war es wieder da.

Ich finde das nicht. Beschreibe bitte den Weg dorthin.
Ich vermute, das ist das gleiche Add-In.

 

Funktioniert bei mir leider nicht *frown.gif*

Ich verwende Excel 2013:

Entwicklertools -> unter "Add- Ins" klick auf "COM-Add-Ins" -> Haken bei "Inquire" .. neue Registrierkarte entsteht -> klick auf Registrierkarte "Inquire" -> unter "verschiedenes" ist auch dann das Feld "Übermäßige Zellformatierung entfernen"

siehe auch https://support.office.com/de-de/article/Bereinigen...

Müssen beim Add In "Excess Format Cleaner" denn auch alle Zeilen die gleiche Zeilenhöhe haben?

 

Denn weiß ich auch nicht. Ich habe 2010.

Die Liste in COM-Add-Ins ist leer, ich kann nur Dateien laden. Evtl. ist das Office hier auf dem Dienst-Laptop nicht vollständig installiert.

Das weiß ich nicht. Werde kaum die Zeit finden, dass Ding zu analysieren.

 

Na jedenfalls ist mein Problem hiermit auf wundersame Weise gelöst und das Thema kann geschlossen werden *Smilie *Smilie