Excel: Makro für Zeilenlöschung, wenn mehr als 2 folgende Zeilen leer oder 0 sind.

Hallo Leute und liebe potentielle Helfer,

wie der Topicname schon sagt, brauch ich Hilfe bei einem Makro oder ähnlichem für Excel.

Es geht um folgendes. Das Makro soll Zeilen durchsuchen, und wenn mehr als 2 aufeinanderfolgende Zeilen leer sind, sollen die folgenden Leerzeilen gelöscht werden, sodass wenn zum Beisppiel 5 Leerzeilen sind, dann nurnoch 2 Leerzeilen bis zum nächsten Text/Tabelle sind.

Ich bin leider blutiger Anfänger wenn es um Excel-Makros geht und hab sowas nahezu Null Ahnung... Ich kann mir nur ein wenig von C-Programmiersprache zusammenreimen.

Ich hoffe ihr könnt mir helfen

Mit freundlichen Grüßen

sTeFaN

 

Im VB-Editor, beim NamenDeinerDatei >>
Menü Einfügen > Modul >>
in dieses eben neu erstellte Modul1 >> diesen Code einfügen >>>

Code:

Option Explicit

Sub DritteLeereZeileEntfernen()
    'löscht leere Zeilen im markierten Bereich, wenn >2 leere Zeilen untereinander
Dim i As Long

            'Bildschirmflackern und Neuberechnung verhindern > zum Beschleunigen
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False

    For i = Selection.Rows.Count To 3 Step -1
            'wenn beide oberen Zeilen auch leer sind
        If WorksheetFunction.CountA(Selection.Rows(i & ":" & i - 2)) = 0 Then
            Selection.Rows(i).EntireRow.Delete
        End If
    Next i

            '...und wieder einschalten
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With

End Sub

Funktion >

- ganze Tabelle markieren (alle Zeilen UND alle Spalten)

- Makro ausführen

- jede dritte, innerhalb der Spaltenmarkierung leere Zeile wird entfernt


Teste das an einer Kopie Deiner Nutzdatei.....

Gruss Urs

 

Hallo, also erstmal danke für die Hilfe, jedoch funktioniert das Makro irgentwie nicht oder ich bin grad zu doof. Ich schildere mal meine Vorgehensweise. Hab das Makro wie von dir beschrieben erstellt.
Das sind dann so aus:


Nun gehe ich bei Excel auf Tools->Marko->Run...
Dann wähle ich das Marko aus und geh auf Run. Aber komischer Weise passiert nichts.


Nochmal eine Verständnisfrage: Versteh ich deinen Quelltext richtig, dass es jedesmal eine Zeile löscht wenn 3 aufeinanderfolgende leere Zeilen gibt?

Im Endeffeckt geht das doch so nicht? Es sollen ja wenn zum Beispiel 6 leere Zeilen/oder mit 0 gefüllte Zeilen aufeinander folgen praktisch 4 gelöscht werden, damit nurnoch 2 am Ende da sind.

Mit freundlichen Grüßen

 

Ahh ich hab mich vermacht sorry, das Makro funktioniert doch soweit.

Bloß wie oben schon beschrieben löscht das Makro nur jede 3. Zeile, das heißt bei 6 leere Zeilen bloß 2, statt 4 .... Es soll praktisch wenn mehr als 2 aufeinanderfolgende Zeilen leer oder Null sind alle nachfolgenden leere Zeilen löschen.

Desweiteren ist das Problem, dass das Makro nur wirklich leere Zeilen löscht. Kann man es auch irgentwie hinbekommen das es auch sowas löscht?... Beispiel: In einer Zelle/Zeile steht =IF(B29="x";A29;"") und die If Aussage trifft nicht zu, sodass in der Zellen dann "" steht, das sowas auch gelöscht wird?

Sorry, ich weiß ich hab viele Ansprüche

Hoffe ihr könnt mir helfen...Danke im Vorraus

 

Das Makro arbeitet genau nach Deiner Vorgabe >
- es löscht soviele komplett leere Zeilen, damit nur zwei davon untereinander stehen können.

Es beginnt unten im gewählten Bereich > wenn diese Zeile und die darüber liegenden zwei leer sind, wird die unterste entfernt.
Dann kommt die nächst höhere Zeile dran...
Von 3, 6, 10, 100... zusammenhängenden leeren Zeilen bleiben immer nur 2 übrig.

Bei mir funktioniert es wie es soll. Deine Zeilen sind vermutlich nicht leer, oder sie haben irgendeine spezielle Eigenart/Format...

Man kann natürlich auch Zeilen mit bestimmten Inhalten entfernen.
Da musst Du aber schon genau erklären, welche Bedingungen auch zum Löschen führen sollen...
...sonst löscht man schnell etwas, was man eigentlich nicht wollte...

Gruss Urs

 

Hi,

ja stimmt ich hatte besondere Formatierungen der Zellen, jetzt funktionierts !! Danke schonmal für den Part.

Hier jetzt noch ein weiteres Anliegen mit dem Löschen:


So soll das später mal aussehen: Man hat am Anfang eine Tabelle (Praktisch als Übersicht), und dort trägt man nur ein "x" oder "-", also ob man die Funktion haben möchte oder nicht. Je nach dem was eingetragen ist, wird dies nach unten verknüpft (zu der komplexen, ausführlichen Darstellung.)

In diesen (auf dem Bild im Moment scheinbar leeren Zellen A19-22) stehen Verknüpfungen wie zum Beipsiel in A19:

=IF(B8="x";A8;"")

das heißt ich brauch noch was, was auch die Zeilen löscht wo halt als Ergebnis der If-Anweisung so ein "" als FALSE-Ergebnis herauskommt.

Der Grund dafür ist ganz einfach, würde ich die Zeilen nicht löschen hätte ich später riesige Mengen an leeren Zeilen zwischen den ausführlichen Schutzfunktionen(da wo jetzt nur einzelne Zeilen sind kommen später ganze Tabellenmengen hin).


Ich hoffe mein Problem ist irgentwie verständlich.

MfG sTeFaN

 

Ich verstehe das etwa so:
1. das ist eine Vorlage, die alle möglichen Zeilen enthält, was nicht gebraucht wird, wird dann entfernt
2. die Zeilen 16-24 sind jetzt nur Ueberschriften, zu jeder kommen noch "Unterzeilen"
3. zwischen den einzelnen Ueberschriftblöcken sind dann die zu Beginn erwähnten 2 Leerzeilen.

Etwa so?

zu 2 > Wann und wie kommen denn diese Unterzeilen ins Blatt?
Sind diese schon in der Vorlage, oder werden sie nachträglich eingefügt?
Sind diese vorerst nur als Leerzeilen im Blatt?

Wann sollen denn die Zeilen entfernt werden?
Zu Beginn, wenn noch keine Werte eingetragen sind, oder wenn die Tabelle fertig ausgefüllt ist?

Mein Makro löscht nur Zeilen, die innerhalb der Markierung leer sind. Wenn ausserhalb der Markierung etwas steht, würde diese Zeile trotzdem gelöscht (falls innerhalb leer).
Vielleicht lässt sich damit etwas machen > dem Makro befehlen, welche Spalten es berücksichtigen soll...

Sonst könnte das Makro, vor dem allgemeinen Zeilenlöschen, die Zellen B5:B13 abfragen und unten die die Zeilen ohne X, aber mit Formel, zuerst löschen...

Na... so ganz habe ich die Funktion des Blattes wohl doch noch nicht begriffen...

Gruss Urs

 

Ja genau, und hier hab ich das ganze dann noch in der Anschaulichen Darstellung:


Die rote Einkreisung stellt dabei die Art der Verknüfpung dar, d.h soo soll dann die ganze Tabelle(lila Umkreisung) verknüpft sein, sodass es am ende nur erscheint wenn oben das x drin steht.
Dann habe ich noch die berüchtigten 2 Freizeilen markiert, die immer übrig bleiben sollen

Die Unterzeilen sind zum Beispiel eine Tabelle wie im Bild dargestellt, dabei ist dann zum Beispiel Tabellenkopf vorgegegebn und Werte werden später manuell eingegeben.

Es soll so ablaufen: Man füllt zuerst die Überste Übersichtstabelle aus (mit den Kreuzen und -), dann entsteht praktisch das Dokument mit den Sachen die man haben möchte, das Makro soll nun auf Knopfdruck ein bisschen Ordnung in die Sache reinbringen, also alles zusammenrücken wenn man zum Beispiel im Extremfall nur die erste und letzte Schutz-Funktion haben möchte.

Zum Schluss hat der User das Dokument (alles fertig zusammengerückt) und kann nun wunderbar seine Werte und Messungen eintragen.

Soo ich hoffe es war verständlich

MfG sTeFaN

 

Die Fantasie beim Entwerfen kennt keine Grenzen, zum Nachvollziehen reicht meine Fantasie manchmal nicht...
Blub hat wohl kaum die einzige Erklärung, die ich gefunden hatte > Blub
Aber sonst ist schon einiges klarer.

Was ist Tabellenkopf? Alles was in Spalte A steht, oder nur die per Formel übernommenen Titel-Zellen?
Was ist Blub1-6? Sind das in der Vorlage schon enthaltene Zeilentitel, die Werte wären dann rechts ab Spalte C?
Oder sind das schon Werte?
Wie kommen dann die dafür benötigten Zeilen ins Blatt? Oder sind diese schon in der Vorlage?


Erklärung > Mir schwebt etwas anderes vor >

- die Vorlage enthält alle möglichen Zeilen
- in der Vorlage jedem Abschnitt einen Namen geben.
zB Bereich A23-25 inklusive der zwei Leerzeilen heisst "AnfKurzSchlussStrom"..... oder
wenn Unterzeilen auch vorgegeben sind, zB A17-22...

Das Makro würde dann den Bereich B5-13 abfragen und für jede Zelle ohne X die Zeilen des ihr entsprechenden benannten Bereiches entfernen.

Gruss Urs

 

Also das "Blub" war nur als Beispiel gedacht und soll für irgentetwas Beliebiges stehen. Das war grad das erste was mir da so eingefallen ist und es hat keinerlei sinnvolle Bedeutung. Wusste ja nicht das du gleich nachforschst was Blub heißt

Also das wär natürlich total geil, wenn ich das so ausdrücken darf.
Das wär ne richtig gute Lösung, ich wusste allerdings nicht das man ein Excelblatt in Bereiche einteilen kann und diese Benamen kann.

Ich glaube diese Lösung wär sogar noch viel optimaler für mein Vorhaben, denn das würde bedeuten, dass ich in einen solchen Bereich beliebige Zellen mit beliebigen Inhalten haben kann, da ja alles auf das "x" von der oberen Tabelle abhängt oder?

Bräuchte in dann überhaupt das Makro???? Ist es möglich den eingeteilten (selbst festgelegten und benannten Bereich) mit einer If-Abfrage verknüpfen? Also Wenn "x" dann is der Bereich vorhanden und wenn nicht dann nicht?...hmh ich glaub ich stell mir das grad zu einfach vor

Das was in Spalte A steht sind praktisch nur Überschriften, dass hat noch nichts mit den Tabellen darunter zu tuen.
Hier mal ein richtiges Beispiel, was ich unteranderem heute erstellt habe.
So wär zum Beispiel der Punkt Einschaltstrom.


Rot eingerahmt wär dann der zu definierende Bereich, und das blaue wären wieder die 2 berühmten Freizeichen.
In dieser Tabelle sind einmal die leeren Zellen, später durch Messung etc zu bestimmen und dann manuel einzutragen, das andere ist vorgegeben.
Dies ist aber wie gesagt nur ein BEISPIEL.

Und Blub 1-6 standen praktisch für Text oder Zahlen.Also Platzhalten ohne Bedeutung^^. ok?

Die Vorlage soll also komplett mit den maximalen Füllstatus an Daten sein und durch dieses "x" oder "-" schaltet man dann Modul einfach nur aus und dort sind ja dann Leerzeilen, bzw Zeilen bei denen die If Abfrage nicht erfüllt ist (zBsp: =IF(F6="x";A6;"")). ( das ursprüngliche Problem )



OK NOCHMAL ZUSAMMENGEFASST:
Wie von dir schon gesagt wär es noch viel optimaler mit diesen Bereichen das zu machen, jedoch hab ich da ehrlich gesagt keine Ahnung wie das geht.^^

 

Bei MilliAmpère ist bei mir Schluss, hätte ja sein können, dass MicroAmpère = Blub...

Bilder der Tabelle sind gut für die Uebersicht, aber nicht zum Bearbeiten.
Ich muss dann die Tabelle rekonstruieren... so gut es geht...

Hier ein Vorschlag. Es ist eine normale XLS, nur das .txt entfernen.
Das Forum akzeptiert keine .xls

Der aufwendigen Darstellung im letzten Bild nach zu schliessen, kann eigentlich nur ein komplette Vorlage mit allen möglichen Zellen vernünftig sein. Bemerkungen sind in der Datei.

Teste ob das brauchbar ist, oder ob ich wieder blub...

Gruss Urs

 

Aja es ist nahezu Perfekt genauso wollte ich das haben..... DANKE , du bist mein Retter

Jetzt nochmal kurz zusammengefasst:

Wie ich ein Bereich festlege hab ich verstanden^^

Das mit dem Button is auch ne sehr elegante Lösung, ich wusste nichtmal das sowas geht, aber Excel kann irgentwie viel mehr als ich denke..hehe

Nun zum Inhalt des Buttons bzw Makros:

Aso ich glaub ich habs verstanden... Für das Makro ist also nur der Bereich der Spalte A interessant, und Spalte B etc interessieren das Makro gar nicht.. ah ok ok.....alles klar....

OK HABS VERSTANDEN

Nun wär es aber natürlich viel cooler, wenn das Makro nach dem Begriff sucht, der jeweils in A5-A13 steht und nicht nach den Buchstaben.

Geht das einfach zu ändern oder würde das Makro dann nur nach dem 'E' von Einschaltstrom suchen? Oder is das zu kompliziert nach einem Wort statt nen Buchstaben zu suchen?

Dann müsste man doch diese Zeile ändern oder?
strX = Left(xCell.Offset(0, -1).Value, 1)
die letzte '1' sagt ja das das erste Zeichen ausgelesen werden soll oder? und xCell.Offset(0,-1) sagt in welcher Zellen das ausgelesen werden soll (hier eine Zelle nach links).
Wenn ich jetzt diese hintere '1' zBsp auf 100 setze, dann liest er doch 100 Zeichen aus?! Die Frage wäre dann natürlich ob man dann die Bereiche auch mit Zeichen benennen kann also zum Beispiel sowas "Anfangs-Überspannung U>>>"
Das ging nicht eben, als ich eben ein wenig rumprobiert habe.

Wie gesagt ist kein Beinbruch wenn das jetzt nicht so umsetzbar ist oder zu kompliziert wird, das was ich eigentlich wollte funktioniert ja eigentlich schon

MfG und schonmal großes Dankeschön

 

Vergiss das was ich oben geschrieben habe !!!

Ich hab das mit den Buchstaben gemacht und das funktioniert TOP !!!!

Was mir allerdings aufgefallen ist. Wenn man ausversehn auf den Button kommt gibts es keine Möglichkeit irgentwas rückgängig zu machen oder????
Gibt es vielleicht die Möglichkeit So ein Rückgängig-Button oder/und Resetbutton(der kopiert dann praktisch nur Bereiche zum Beipsiel Beireich A von Excelsheet 2 in den Bereich A des Excelsheet 2) zu erstellen.
Hatte mir gedacht es könnte eventuell gehen, dass man noch ein weiteres Excelblatt erstellt und dies also Sicherung verwendet, d.h dass dort auch alle detailierten Tabellen vorhanden sind. Wenn man nun ausversehn was löscht oder später irgentwas doch noch erweitern oder hinzufügen möchte kann man dieses über diesen Button tun?!

ich denk schonwieder voll exotisch ^^...naja du musst wissen ich soll das Excelzeug für die Arbeit erstellen und es soll praktisch eine Angebotserstellung "automatisieren", und da kann es ja hin und wieder vorkommen das der Kunde plötzlich mehr haben möchte als ursprünglich geplant, dann müsste man ja alles nochmal neu machen? ohhh.....

 

Excel ist unglaublich umfangreich. Ich denke nicht, dass überhaupt jemand alle Möglichkeiten im Kopf haben kann... ausser vielleicht einigen Autisten bei Quizz-Shows...
Erst das Gefühl über Was-Wie-Wo-Suchen hilft... aber auch nur mit passablen Englischkenntnissen.
Immerhin konntest Du ja leicht alles richtig nachvollziehen...

1. Die vorgesetzten Buchstaben bei der Auswahl >
Als Benannten Bereich kann man den ganzen Zellinhalt nicht verwenden, zB sind für Namen keine Leerzeichen erlaubt.
Ich würde es aber auch auf Umwegen nicht machen wollen > sehr fehlerträchtig.
Wenn oben auch nur ein Leerzeichen geändert wird...muss zwingend auch der Bezug zum Bereich geändert werden.
Wenn die Buchstaben wirklich stören sollten, kann man sie alleine in eine später ausgeblendete Zusatzspalte schreiben... und das Offset entsprechend anpassen.

2. Aus Versehen den Button auslösen >
Klar, während des Bearbeitens der Vorlage hatte ich genau deshalb im Makro die Zeile
'ThisWorkbook.Names(strX).RefersToRange.EntireRow.Delete
mit dem vorgesetzten Apostroph vorübergehend deaktiviert.
Makros sind gut, aber sie sind immer gut, auch wenn man das einmal nicht möchte...
Man könnte aber eine Bestätigungsfrage einfügen > "Wollen Sie das Zeug wirklich entfernen ?"

3. Genaue Kopie des Blattes Vorlage in der gleichen Mappe >
Das gibt Aerger! Dann wären die benannten Bereiche und das Makro zweimal in derselben Arbeitsmappe.
Zum Sichern der Vorlage eine Sicherungskopie der ganzen Datei unter anderem Namen erstellen.

4. Rückgängig machen des Zeilen-Entfernens in der Tochter-Mappe >
Hatte ich irgendwo geschrieben > "weg ist weg"
Aber richtig, man könnte des Entfernte irgendwo sichern... und dann wieder einfügen lassen.
Auf die Schnelle fällt mir jetzt aber keine sinnvolle und brauchbare Lösung ein...
...ich werde mir das noch durch den Kopf gehen lassen.

>>> Die Tochter-Mappe geistert doch nicht irgendwo in der Welt herum, beim Kunden etwa?
Sie bleibt immer bei Euch?
Der Kunde erhält nur den Ausdruck?

Gruss Urs

 

Ja is mir beim Ausprobieren/Rumprobieren auch aufgefallen...Hab das letztere schon gemacht mit der Extraspalte + Offset ändern

So ne Abfrage wär echt vorteilhaft.

Ja der Kunde erhält nur den Ausdruck, das was mir durch den Kopf gegangen ist, wär ein Situation bei der der Kunde zunächst sagt er will das und das und man macht das Angebot fertig und dann hat man die Datei ja gespeichert. Dann fällt dem Kunden auf einmal ein: Oh ich brauchen noch das und das auch noch. Das war jetzt mein Hintergrundgedanke...

Aber ich hab mir schon gedacht, dann kann ich einfach noch nen Revisionssheet erstellen mit den gleichen Funktionen, dann hat man zwar 2 Blätter aber das is dann auch egal.


ZUSAMMENFASSUNG:

Urs, erstmal herzlichen Dank für alles...

Die Abfrage zur sicherheit wär noch wunderbar, wenns nichts zu kompliziertes ist und keine Umstände bereitet^^

Ja das mit der Sicherung kannst dir ja mal durch Kopf gehen lassen, is aber nicht schlimm wenns nicht klappt.

Noch eine Abschlussfrage: Hab grad das Problem das es in manchen Fällen dazu kommt, dass ein Seitenumbruch direkt in einer Tabelle ist, kann man das irgentwie einstellen, wo "mögliche Seitenümbrüche" sein sollen?

Mit freundlichen Grüßen

Stefan

 

Hallo Stefan,

Füge diese roten Zeilen ins Makro ein, dann kommt die Abfrage für "Vergessliche" >>> wenn Cancel, dann Exit >

Code:

Private Sub cbnBlattVorbereiten_Click()

Dim rngAuswahl As Range, xCell As Range, strX As String

[COLOR="Red"]If MsgBox("Soll dieses Zeug wirklich in den Orkus verschwinden ?     ", vbOKCancel, _
            "Achtung  !") <> 1 Then Exit Sub[/COLOR]

Set rngAuswahl = ThisWorkbook.Names("Auswahl").RefersToRange

For Each xCell In rngAuswahl

Das erleichtert das Leben enorm... die grundsätzliche Erklärung hatte ich schon vorher verstanden...


Das "Rückgängig machen..." >>>
Wenn mir ein prinzipieller Lösungsweg nicht blitzartig einfällt >>> Abschalten + Warten auf den Kugelblitz...
...letzterer müsste nächstens einmal zuschlagen... oder nie mehr...

Gruss Urs


Gerade noch gesehen >
Ich fühle mich eher für die Tabelle einigermassen begabt, das Ausdrucken >>> andere Baustelle...
Unter Seitenansicht kann man ja vorgeben wo der Umbruch stattfinden soll...
...wenn die Tabellengrösse aber durch Makros während deren Laufzeit verändert wird > keine Ahnung!

Vielleicht weiss ein Zuschauer mehr darüber...

 

Die leuchtende Idee kam nicht.
Für eine perfekte Lösung wäre das für mich viel zu viel Arbeit... mit unsicherem Ausgang...

Vielleicht reicht eine abgekürzte Form >
- der User macht im Bereich "Auswahl" ein Kreuz und startet ein Makro
- der ausgewählte Bereich wird oben den vorhandenen Bereichen vorangestellt..
- das vorhandene Makro, zum Entfernen von Bereichen, funktioniert für diesen neuen Bereich nicht, er hat ja keinen Namen...

Gruss Urs

 

Ja das mit der MsgBox funktioniert alles.

Ok ja dann mach ich erstmal normal weiter

Also Danke für alles Urs....Fals ich nochmal auf irgentwas "kompliziertes (für mich ) stoßen sollte, melde ich mich

Ja fals ein Zuschauer eine Lösung zu meinen Seitenumbruchproblem mitten in einer Tabelle hat kann ers gern hier posten

Gruß

Stefan

 

Hallo Stefan,

Das Wiederherstellen von vorher entfernten Datensätzen ist eigentlich eine Funktion von missratenem Kundenverständnis.
Auch eine abgespeckte Wiederherstellung wäre nicht mehr... als eine Flickschusterei... die den Aufwand nicht wert ist.

Ich würde bei mir in der Vorlage >

> eine Kopie aller Datensätze weit unterhalb der jetzigen Datensätze anlegen >>> aber ohne Bereichsnamen !

Wenn das seltene Ereignis einer nötigen Rekonstitution eintreten sollte >
> kann der User oben mit Zeileneinfügen Platz schaffen und >
> den unten vorhandenen Bereich nach oben kopieren

Wenn der User dafür zu blöd sein sollte, darf er eine neue Tabelle anlegen...
...und zur Strafe die schon vorhandenen Werte nochmals eintippen.

Mit echten Problemen darfst Du Dich natürlich jederzeit hier wieder melden...
...ich helfe Dir gerne weiter... wenn ich kann... und solange mir das gewünschte Resultat sinnvoll erscheint...

Gruss Urs

 

Mir ist eben noch etwas völlig Anderes durch den Kopf gegangen.

Gibt es die Möglichkeit eine Überprüfung einzubauen (also in den Button)?
Also, dass beim klicken des Buttons zunächst überprüft wird, ob in dem Bereich "Auswahl" auch jeweils ein "x" oder "-" drin steht?
Fals ein Feld leer ist oder etwas anderes als "x" oder "-" drinsteht würde dann ne Meldung kommen "Bitte füllen Sie die Tabelle komplett mit x oder - aus!"

Dies würde Rechtschreib-, Klick- oder Vergessfehler ausschließen.

Also irgentwie sowas:

If (Auswahl = "x" oder "-")
[bisherigen Code ausführen]
else
[Message, dass zunächst alle Zelle des Bereichs "Auswahl" mit "x" oder "-" gefüllt werden sollen!]

Bloß halt in Excel-Sprache

Stefan