Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Excel - leere Zellen automatisch löschen





Frage

Hallo, ich habe eine recht umfangreiche Tabelle (~180x3500 Zellen), die jeweils in 2 benachbarten Spalten zusammengehörige Zahlenwerte enthält. Allerdings nur jeweils 40 richtige Werte pro Spalte - der Rest besteht aus Nullen, die über oder unter den gewünschten Daten liegen. Nun brauche ich eine Funktion, die mir die Zellen mit dem Wert Null entfernt und die verbleibenden Zellen der Spalte jeweils nach oben verschiebt. Ein Sortieren der zu verschiebenden Werte verbietet sich leider. Gibt es für sowas eine Funktion, oder kann man das mit einem Makro erledigen? Vielen Dank schonmal!

Antwort 1 von schnallgonz

Hallo,
es ist ausgeschlossen, dass in den jeweils zusammenhängenden Spalten innerhalb der 40 Werte eine NULL enthalten ist?
Wenn ja, also immer 40 zusammenhängende Werte ohne Nullen, kann man alle Zellen mit Inhalt "0" durch "" also NIX ersetzen und diese Leerzellen dann über specialcells löschen.
Bevor Du aber mit dem Makro rumfuchtelst, erst Datei sichern!

Sub leerWechXlupSpalte()
´ersetzt alle Zellen mit 0 durch "", löscht dann alle
´Leerzellen je Spalte und schiebt dabei spaltenweise
´wichtig: jede Spalte muß eine Überschrift haben
´*** by schnallgonz 04.01.2007 ***

Dim esp As Integer, i As Integer

´erstmal alle Nullen durch Nix ersetzen
Cells.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
    SearchOrder:=xlByRows, MatchCase:=False
      
´letzte Spaltenüberschrift ermitteln
esp = Range("IQ1").End(xlToLeft).Column

´Schleifenstart, Spaltenweise leere Zellen löschen
´und Rest nach oben verschieben
For i = 1 To esp
Range(Cells(2, i), Cells(65536, i)). _
    SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
Next i

End Sub


makro als Modul anlegen, evtl per Button starten.
Hochkomma sind Kommentarzeilen, werden hier als Apostroph dargestellt, bitte im Code in Hochkomma ändern.

gruß
schnallgonz

Antwort 2 von iridium

Hallo schnallgonz


erstmal ganz herzlichen Dank für die schnelle antwort.
ich bekomme mit dem skript allerdings eine fehlermeldung:

Laufzeitfehler 1004 - Keine Zellen gefunden.

Wähle ich dann Debuggen, zeigt er mir die zeilen beginnend mit ´range´ und ´specialCells´ in gelb an; ein pfeil deutet dabei auf die 2. Zeile (beginnend mit ´specialCells´).

Was kann ich ändern?

Grüße, iridium

Antwort 3 von iridium

Sorry - Falscher Alarm: Das Macro geht doch!!!

Allerdings nur, wenn es sich bei den Daten um ´harte´ Werte handelt. Bei meinen ursprünglichen Daten hatten alle Zellen der Tabelle eine ´Wenn´ Funktion zur Grundlage. Oder hätte es damit auch gehen müssen?

Auf jeden Fall werde ich mich bei nächster Gelegenheit mal mehr mit VB beschäftigen. Toll was damit alles möglich ist!

Dank und Gruß,
iridium

Antwort 4 von schnallgonz

Hi iridium,
ich mach jetzt ersma mhd :-)
morgen sehen wir weiter, weil:
neuer Sachverhalt:
Zitat:
leere Zellen automatisch löschen

sind aber nicht leer, sondern
Zitat:
der Rest besteht aus Nullen

stimmt aber auch nicht, sind
Zitat:
alle Zellen der Tabelle eine ´Wenn´ Funktion


Du siehst, in VBA muss man manchmal Erbsen zählen...
Kann man bestimmt mit einer Schleife lösen, dauert nur wesentlich länger als mit specialcells
Vielleicht fällt einem der nachtschwärmer noch was ein, ich überlege morgen weiter

gruß
schnallgonz

Antwort 5 von iridium

Hallo schnallgonz - und natürlich alle anderen geneigten Leser...

Da hatte ich sogar schon versucht, mich präzise zu formulieren. Asche auf mein Haupt...
Zunächst: Das Skript funktioniert soweit. Nach einigen Stunden Trial-and-Error mit VBA bin ich sogar soweit, dass ich mit dem Ergebnis der Bastelei arbeiten kann.

Allerdings - wie Du ja erkannt hast: Eine Zwischenstufe in der Verarbeitung der Daten ist bei mir eine ´Wenn´ Bedingung, die die ursprünglichen 3500 XY-Koordinaten auf unhandliche ~700.000 Zellen aufbläht (bitte nicht lachen!). Das ließe sich bestimmt mit VBA cleverer lösen und vielleicht kann mir jemand einen Tipp geben.

Die Daten haben das Format:

Spalte A: Probennummern 1-85 jede Nummer ca. 20-41fach, alles bündig untereinander
Spalte B und C: X-Y-Koordinaten, zu den jeweiligen Nummern gehörig und müssen dieser zugeordnet bleiben

Die Daten sollen in das Format:

Spalte A: X-Koordinaten für Probennummer 1
Spalte B: Y-Koordinaten für Probennummer 2
Spalte C: X-Koordinaten für Probennumemr 3
ect.

Falls Du oder jemand anderes eine Idee hat, die die Dateigröße auf irgendwas unter 35 MB (!) bringt - ich würde mich freuen!

Viele Grüße und ein schönes WE,
Iridum

Antwort 6 von schnallgonz

Hallo iridium,
für das Ausgangsproblem ist mir eine Lösung eingefallen, aber die Fragestellung hat sich geändert.
Das Speichervolumen hängt natürlich an der Formelanzahl, Matrixformeln könnten schon Besserung bringen.

Die Aufgabenstellung ist mir leider nicht ganz klar.
Was ist z.B. mit der Y-Koordinate der ProbeNr1?
  1. Ausgangspunkt ist nur die übersichtliche Menge von 85 Proben mit den jeweiligen x-y-Koordinaten, d.h. 86 Zeilen (incl Überschriften) in den Spalten A bis C?
  2. diese Werte sollen im Prinzip nur transponiert werden, und zwar im Wechsel jede 2. Zeile und in ein neues Blatt?
  3. Ist das eine einmalige Aktion oder immer wiederkehrend mit neuen Proben, die in die nächste freie Zeile tranponiert werden?
Du siehst, zunächst wieder nur Sachverhaltsaufklärung.
Bis dann
gruß
schnallgonz

Antwort 7 von iridium

Hallo Gert, hallo Forumgemeinde,

Zitat:
Die Aufgabenstellung ist mir leider nicht ganz klar.
Was ist z.B. mit der Y-Koordinate der ProbeNr1?

Sorry - Flüchtigkeitsfehler; es sollte heissen:

Die Daten sollen in das Format:

Spalte A; Zeilen 1-41: X-Koordinaten für Probennummer 1
Spalte B; Zeilen 1-41: Y-Koordinaten für Probennummer 1
Spalte C; Zeilen 1-41: X-Koordinaten für Probennummer 2
Spalte D; Zeilen 1-41: Y-Koordinaten für Probennummer 2

und so weiter bis Spalte ´FN´ mit den Y-Koordinaten von Probe 85.

So ergibt sich also bei 85 Probennummern mit jeweils max. 41 X-Y-Koordinaten eine neue Tabelle mit 170 Spalten und (abhängig von der Probennummer max.) 41 Zeilen. Die Koordinaten müssen bei Proben mit <41 Koordinaten oben bündig eingetragen werden.

Zitat:
Ausgangspunkt ist nur die übersichtliche Menge von 85 Proben mit den jeweiligen x-y-Koordinaten, d.h. 86 Zeilen (incl Überschriften) in den Spalten A bis C?


Bei max. 41 Koordinaten-Paaren je Probennummer max. 3486 Zeilen (incl. Überschrift), in der Praxis aber eher ~2800 +/- 300. Tatsächlich ein paar Spalten mehr, da zusätzliche Parameter enthalten sind, die ich aber nicht benötige.

Zitat:
diese Werte sollen im Prinzip nur transponiert werden, und zwar im Wechsel jede 2. Zeile und in ein neues Blatt?


Da komm ich jetzt nicht ganz mit. Also die Werte sollen in ein neues Blatt transponiert werden und zwar in Spalten abhängig von der Probennummer 1-85, die in Spalte B neben den jeweiligen Koordinaten steht. Spalte A enthält (und das sollte ich genau nehmen, wie ich jetzt gelernt habe) eine laufende Nummer (1- max. 3485) über alle Proben und Punkte.

Zitat:
Ist das eine einmalige Aktion oder immer wiederkehrend mit neuen Proben, die in die nächste freie Zeile tranponiert werden?


Sagen wir ´einmalig´. Diese Datei wird eine Durchgangsstation, in die immer ein ´fertiger´ Datensatz vom beschriebenen Format reinkopiert und transponiert wird. Ich wollte die Datei schreibgeschützt verwenden. Die transponierten Daten werden dann in einer anderen Tabelle benötigt.
Für mich ist diese Aktion allerdings sehr häufig wiederkehrend - sonst hätte ich nicht an das Forum gewendet, sondern mich einmal durchgerackert.
Daher wäre es auch vorteilhaft, wenn die Datei nicht soo groß ist, denn da geht der Rechner schon in die Knie.

Viele Grüße aus München,
Iridium

Antwort 8 von iridium

Ich hoffe, mir wird das nicht angekreidet - ich wollte nur den Eintrag nochmal in die höheren Zeilen zurückholen, da ich nach wie vor intensiv nach einer Lösung suche und Angst habe, dass das Posting da unten verstaubt.

viele grüße, iridium

Antwort 9 von coros

Hallo iridium,

sorry, aber ich steige durch Deine Aufgabenstellung, bis auf die Eingangsfrage, nicht richtig durch. Wenn es Dir immer noch darum geht, Wert die eine 0 haben, zu löschen, dann versuche es mal mit Dem nachfolgendem Makro. Bei diesem Makro werden alle Nullwerte in den Spalte A und B gelöscht.

Option Explicit

Sub Nullwerte_löschen()
Dim rng As Range, Zelle As Range, i As Integer
Set rng = Range("A1:B" & Range("B65536").End(xlUp).Row)
For Each Zelle In rng
If Zelle = 0 Then Zelle.ClearContents
Next
Range("A1:B" & Range("B65536").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Cells.Delete
End Sub


Sollte das nicht mehr Bestandteil Deiner Fragestellung sein, weil eventuell erledigt, dann schreibe Deine Frage nochmal besser hier hin, so dass man das auch versteht.

MfG,
Oliver
Da hier der einzige Lohn für die Helfer eine Rückmeldung ist, wäre es nett, wenn Du ein
Feedback abgeben könntest, ob der Lösungsvorschlag Dein Problem gelöst hat.

Antwort 10 von schnallgonz

Hi iridium,
sorry, dass ich mich nicht mehr gemeldet habe, keine Zeit.
Das Hochziehen des Postings ist ok, dadurch hast Du mit Oliver schon mal einen der Leistungsträger dieses Forums an Bord.
Jetzt mußt Du es nur noch hinkriegen, ihm Dein Problem klarzumachen.
Ich will mich nicht von der Beantwortung abmelden, sondern erstmal dein Posting 7 verstehen, melde mich dann wieder.

gruß
schnallgonz

Antwort 11 von iridium

Hi Oliver, hallo Schnallgonz,

ich danke euch zunächst für eure Antworten. Die Fragestellung ist für mich immer noch aktuell. Aber ich fürchte, dass dieses Posting mittlerweile zu unübersichtlich geworden ist. Ausserdem passt die Fragestellung nicht mehr wirklich zu der Überschrift. Daher werde ich ein neues Posting mit einer klar formulierten Probelmstellung (ich gebe mir mühe)eröffnen.

Das Löschen der ´leeren Zellen´, resp. Nullwerte klappt aber schonmal soweit - Dank eurer Hilfe!

Viele Grüße,
iridium

Antwort 12 von schnallgonz

So, da bin ich wieder und sage: Fragen über Fragen.
Der Reihe nach:
Dein Wunsch lautet zunächst
alle Zellen, deren Wert = 0 sollen entsorgt werden.
Dafür hat coros schon Abhilfe geliefert.
Falls Überschriften vorhanden sind, den Range-Befehl erst ab Zeile2 setzen, sonst Typenunverträglichkeit und anpassen auf Spalten C:D.

Problem1, das ich jetzt sehe, also vor dem transponieren:
In SpalteA steht die lfdNr in SpalteB die ProbenNr
Wenn jetzt die Nullwerte spaltenweise entsorgt werden, bleiben die lfdNr und ProbenNr stehen und die Werte allein werden nach oben geschoben.
Ergo: falsche Zuordnung der lfdNr und ProbenNr und jede Menge Nr ohne Koordinaten (=Müll)
Damit wären wir doch beim Klassiker, alle Zeilen löschen, in denen eine Null steht, oder?
Also, sobald in Spalte C eine Null = entsprechende Zeile löschen, Abfrage für Spalte D entbehrlich.

Wenn diese Arbeit erledigt ist, könnte man transponieren, wenn die nächsten Fragen geklärt sind:
die Reihenfolge der 41 Koordinaten je ProbenNr darf nicht verändert werden, oder?
Die ProbenNr stehen numerisch sortiert 1-85 mit je einem Wert für Koordinaten1, dann wieder 1-85 mit Koordinaten2 usw.?
Dann müßte man also grundsätzlich Zeilen 2-86 in Zeile 1 eines neuen Blattes transponieren, Zeile 87-171 in Zeile 2 usw.
Aber Problem2:
durch die Entsorgung der Nullwerte sind zahlreiche ProbenNr nicht mehr in jedem Durchgang enthalten.
Man kann also nicht einfach so transponieren, weil dann im neuen Blatt die Spaltenzuordnung nicht passt.
Man müßte also nach ProbenNr sortieren und innerhalb dieser ProbenNr nach lfdNr aufsteigend.
Dann die erste ProbenNr mit allen Koordinaten in Spalten A und B kopieren, die nächste ProbenNr in Spalten C und D usw

@irirdium
Bevor es weiter geht, bist Du wieder dran.
Stimmt meine Interpretation des Sachverhalts?

Wenn ja, hoffe ich auf coros, dem der passende code dazu als VBA-Profi sicher leichter fällt als mir.

gruß
schnallgonz

Antwort 13 von iridium

@schnallgonz

Danke für Deine neue Antwort. Ich habe mittlerweile ein neues und hoffentlich unmissverständlichere Posting eröffnet - vielleicht können wir die Fragestellung dort weiterdiskutieren?

LG, iridium

Antwort 14 von coros

Hallo an alle,

damit auch jemand, der durch die Forensuche zu diesem Beitrag gelangt, weiß wie es weitergeht, hier der Link zu dem neuen Beitrag.

Link zum neuen Beitrag

Es sollte wenn möglich hier dann auch keine weiteren Lösungsvorschläge mehr gepostet werden.

MfG,
Oliver
Jeder macht was er will, keiner macht was er soll, aber alle machen mit.

Ich möchte kostenlos eine Frage an die Mitglieder stellen:


Ähnliche Themen:


Suche in allen vorhandenen Beiträgen: