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

Hallo Stefan,

Mein Makro hatte nur die X (oder x) verwendet, was sonst in der Zelle stand, oder nicht stand, war ihm egal.

Füge diese roten Zeilen ins Makro ein >

Code:

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

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

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

[COLOR="Red"]For Each xCell In rngAuswahl
            'prüft ob entweder "X" (oder "x") oder "-" in jeder Zelle steht
    If LCase(xCell.Value) <> "x" And LCase(xCell.Value) <> "-" Then
        MsgBox "In JEDER Auswahl-Zelle muss entweder stehen >    " & vbCr & vbCr & _
        "> X <  wenn dieser Teil verwendet werden soll, oder    " & vbCr & _
        "> - <  wenn dieser Teil verworfen wird" & vbCr & vbCr & _
        "Um Fehler zu verhindern werden andere Zeichen" & vbCr & _
        "oder keine Eingaben nicht akzeptiert...", _
        vbOKOnly, _
        "Falsche Eingabe...  !"
            'verlässt Sub
        Exit Sub
    End If
Next[/COLOR]

For Each xCell In rngAuswahl
            'wandelt zuerst grosses X in kleines x... für alle Fälle...

Ob Du die erste MsgBox jetzt noch brauchst... Dein Entscheid.


Eben gesehen >
Die Eingabe des Minus-Zeichens (oder Bindestriches) gefällt Excel nicht. Es meint dann, das sei der Beginn eines Zellbezuges...
Ev. andere Zeichen verwenden, damit man auch die Nach-Unten-Taste verwenden kann (zB "j" und "n").

Gruss Urs

 

Danke Danke Urs2

 

irgentwie habe ich grad das Problem, dass diese Meldung immer kommt und nichts passiert....hmh

So hab ich das jetzt:

Code:

Private Sub Makro_Click()

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

If MsgBox("Wollen Sie das Makro zum Erstellen der mit x gekennzeichneten Schutzfunktionen wirklich ausführen?" & vbNewLine & "" & vbNewLine & "Are you sure, that you want to create the protectionfunctions, which are marked with x?", vbOKCancel, _
           "Achtung - Rückgängig nicht möglich! / Attention - Reverse not possible!") <> 1 Then Exit Sub


hmh ....

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

For Each xCell In rngAuswahl
            'prüft ob entweder "X" (oder "x") oder "-" in jeder Zelle steht
    If LCase(xCell.Value) <> "x" And LCase(xCell.Value) <> "-" Then
        MsgBox "In JEDER Auswahl-Zelle muss entweder stehen >    " & vbCr & vbCr & _
        "> X <  wenn dieser Teil verwendet werden soll, oder    " & vbCr & _
        "> - <  wenn dieser Teil verworfen wird" & vbCr & vbCr & _
        "Um Fehler zu verhindern werden andere Zeichen" & vbCr & _
        "oder keine Eingaben nicht akzeptiert...", _
        vbOKOnly, _
        "Falsche Eingabe...  !"
            'verlässt Sub
        Exit Sub
    End If
Next

For Each xCell In rngAuswahl
            'wandelt zuerst grosses X in kleines x... für alle Fälle...
    If LCase(xCell.Value) = "-" Then
            'sucht 5 Zellen nach links > nach den ersten beiden Buchstaben im Text
        strX = Left(xCell.Offset(0, -5).Value, 2)
            'error meldung überspringen, falls Abschnitt schon gelöscht wurde
        On Error Resume Next
            'löscht Abschnitt für dieses X
        ThisWorkbook.Names(strX).RefersToRange.EntireRow.Delete
    End If
Next

End Sub

 

Hallo Stefan,

Ich habe Dein Makro in meine Mappe kopiert und getestet >
- die erste MsgBox kommt immer
- die zweite nur, wenn weder X, noch x, noch - in einer der Zellen steht
....also wie geplant.

Wird die Box bei überall "X", oder überall "-" auch angezeigt? Sollte sie nicht.

Bis zum Anzeigen der zweiten Box hat das Makro ja noch nicht viel geleistet > also prüfen >

> heisst im Blatt der benannte Bereich "Ausgabe" immer noch so, und verweist er auch noch auf die richtigen Zellen ?
Wenn nicht, kommt natürlich zwingend die MsgBox.

> Du hast offenbar ein englisches Excel, ich Deutsch/Schweiz...
Bei X oder x kann eigentlich kein Unterschied sein. Hatte vorher ja auch funktioniert.
Ist das "-" im Blatt die gleiche Type, wie das im Code verwendete ?

...sonst fällt mir im Moment nichts ein...

Gruss Urs

 

Bei mir kommt die zweite auch immer irgentwie....

hmh jaaa^^...kommt einfach immer.....

Jaa is alles noch so wie es vorher war, und ohne dem neuen Teil funktioniert es ja auch alles.

Hab es auch schon mit "j" und "n" probiert.

Najut ich werd erstmal noch ein wenig rumprobieren.
Is wieder nur so ne Kleinigkeit, die ich übersehn habe.
Der Code is ja eigentlich schlüssig in meinen Augen

Ich meld mich wieder.

MfG

 

Hab eben nochmal alles probiert und die ganze Sache nochmal durchdacht....

Kann es sein, dass es deshalb nicht funktioniert weil mein "Auswahl"-Bereich, also die Spalte mit den x und - ,aus 5 verbundene Spalten besteht?
Also 5 Spalten verknüpt zu einer.
Kann das sein, dass er dann nur die Erste Teilspalte da nimmt und die andern als "leer" ansieht, obwohl die Zeilen "verbunden" sind?


Bin zwar nicht soo der Visual Basic Versteher aber bisschen Programmierendenken hab ich ja als Elektrotechnik-Student im 2. Semester ....

Also das wär jetzt noch das einzige was ich mir als Fehlerquelle vorstellen kann. Ansonsten ->

MfG

 

Aber sicher ist es deshalb !
Das Makro prüft alle Zellen im Bereich F6:J29 >> da ist ja die Mehrzahl der Zellen immer leer...

Wenn Du den ganzen Bereich farbig machen willst > Kein Problem.
Aber der benannte Bereich darf nur die Zellen H6:H29 umfassen.

Im Menü Einfügen > Namen kannst Du den Bereich ändern.
Manchmal stellt es auf stur, dann dort den alten Bereich löschen, und neuen definieren.

Gruss Urs

 

jaa ich Doofie ....

aber bin ja fast allein drauf gekommen

Naja alles klar jetzt funktioniert alles....

 

Hallo Stefan,

Ist das Thema "Wiedereinfügen vorher gelöschter Bereiche" noch aktuell ?

Wenn man überhaupt nicht mehr daran denkt... kommt manchmal doch noch ein Gedankenblitz...
...wir hatten uns ja von vornherein auf das Löschen der nicht benötigten Einträge versteift.

Es müsste aber auch mit dem Verstecken der nicht benötigten Zeilen gehen.
Sie sind dann zwar noch im Blatt, aber nicht mehr sichtbar, und können bei Bedarf wieder eingeblendet werden.

Du kannst markierte Zeilen auch manuell verstecken, über Menü > Format > Zeile > Ausblenden
Prüfe so, ob die anderen Funktionen Deines Blattes (zB auch Drucken) dann auch noch wie geplant funktionieren.

Wenn OK, könnte ich das Makro leicht (denke ich mal...) entsprechend ändern.

Gruss Urs

 

Ja ich probier das mal.

Die Idee an sich klingt sehr gut und JA das Thema ist noch aktuell.

Ich kann mich allerdings erst wieder Montag melden, da es einen Sterbefall bei mir in der Familie gab und ich mich erst wieder Montag an die Arbeit machen werden.

Ich berichte dann also Montag. Bis dann !

 

Hallo Stefan,

Mein Beileid.
Möge, nach einigen Tagen, das Leben der Hinterbliebenen wieder so weitergehen, wie vorher erhofft !


...............................


Ich weiss nicht, ob meine Idee nächste Woche immer noch präsent wäre...
...also habe ich heute getestet und hänge das Resultat hier an.
Ich denke... sieht nicht schlecht aus... das Testen beim Drucken überlasse ich Dir...


Gruss Urs

 

Hallo Urs,

sorry , dass ich mich erst jetzt melde, hatte Probleme mit meinem Internet und dann noch Autounfall (A2 Massenkarambolage) am Sonntag.

Also das Makro funktioniert soweit, jedoch hab ich nun Probleme mit meinen Seitenumbrüchen, denn durch das "Verstecken" spielt irgentwie alles bzgl. der Seitenumbrüche verrückt.

Ich probiere nun erstmal ein wenig rum.

Eine Frage hab ich noch:

ActiveSheet.Rows(xRange.Rows.Row & ":" & xRange.Rows.Row + xRange.Count - 1).EntireRow.Hidden = True

Was bedeutet das "xRange.Count - 1"...was macht die "-1"?

 

Hallo Stefan,

Du scheinst ja Pech und Schwefel so richtig anzulocken...

In meiner Beispieldatei müssten für den Bereich "C" die zu versteckenden Zeilen so definiert sein >
ActiveSheet.Rows(32:43)........

xRange.Rows.Row ergibt 32 >> für die erste Zeilennummer des benannten Bereiches "C"
xRange.Count ergibt 12 >> Anzahl Zellen im Bereich >> da Bereich einspaltig >> auch Anzahl Zeilen

Anfangszeile 32 plus Anzahl 12 = Endzeile 44 >>> = Falsch !
Da die Zeile 32 so ja doppelt gezählt würde, muss man deshalb 1 Zeile abziehen...


Die Zeilenumbrüche >
Versprechen kann ich gar nichts, aber ich werde mal herumforschen...

Gruss Urs

 

Ja das kannst du wissen. War nen richtiges Scheiß-Wochenende.
Und mein schöner Golf 5 mit Vollausstattung ist kaputt..... Dabei war er ja erst 1,5 Jahre alt und hatte gerade 20.000Km runter
Das Gutachten ist zwar noch nicht da, aber der Werkstattmeister vermutet schon, dass es Totalschaden ist.

Ahhh jaa Ok . Verstanden.

Ja ich probiere auch noch ein wenig rum.

Wie gesagt es scheint so, dass durch die "manuell gesetzten" Seitenumbrüche Fehler auftreten beim "Verstecken".

 

Wie fügst Du denn diese Umbrüche ein ?
Ueber Menü Einfügen > Seitenumbruch ?
Oder verschieben direkt in der Ansicht Seitenumbruchvorschau ?
Mit so verschobenen Seitenumbrüchen hat das Versteckmakro bei meinem Excel2003 keine Probleme...

Ich weiss jetzt auch schon, wie ich die durch das Verstecken mit dem Seitenumbruch zerrissenen Abschnitte finden kann, und wo ich den neuen Umbruch setzen müsste, nur...
...auf meine Befehle mit xlPageBreakManual oder HPageBreaks.Add reagiert mein Excel einfach nicht.

Ich habe keine Ahnung warum das so ist... vielleicht fällt mir noch etwas ein...

Gruss Urs

 

Ja einfach so verschieben in der Seitenumbruchansicht.

Hab jetzt folgenden Quelltext:

Code:

Private Sub Makro_Click()

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

If MsgBox("Wollen Sie das Makro zum Erstellen der mit x gekennzeichneten Schutzfunktionen wirklich ausführen?" & vbNewLine & "" & vbNewLine & "Are you sure, that you want to create the protectionfunctions, which are marked with x?", vbOKCancel, _
           "Achtung - R&#252;ckg&#228;ngig nicht m&#246;glich! / Attention - Reverse not possible!") <> 1 Then Exit Sub

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

For Each xCell In rngAuswahl
    If LCase(xCell.Value) <> "x" And LCase(xCell.Value) <> "-" Then
    MsgBox "In JEDER Auswahl-Zelle muss entweder stehen >" & vbCr & vbCr & _
        "x  >>  wenn dieser Teil verwendet werden soll." & vbCr & _
        "-   >>  wenn dieser Teil verworfen werden soll." & vbCr & vbCr & vbCr & _
        "Only following entries become accepted >" & vbCr & vbCr & _
        "x  >>  you need this part." & vbCr & _
        "-   >>  you don't need this part.", _
        vbOKOnly, _
        "Falsche Eingabe! / Faulty Entry!"
        Exit Sub
    End If
Next

    ActiveSheet.Rows.Hidden = False
For Each xCell In rngAuswahl
    If LCase(xCell.Value) <> "x" Then
        strX = Left(xCell.Offset(0, -5).Value, 2)
        Set xRange = ThisWorkbook.Names(strX).RefersToRange
        ActiveSheet.Rows(xRange.Rows.Row & ":" & xRange.Rows.Row + xRange.Count - 1). _
            EntireRow.Hidden = True
    End If
Next

End Sub

Ist daran was falsch?
Jedenfalls kommt es irgentwie zu "zufällige" Fehler, also beim Verstecken werden aufeinmal viel zu viel Sachen versteckt, die man gar nicht mit "-" gekennzeichnet hat.
Grafisch:

Nur eins mit "-" gekennzeichnet.

Das Ergebnis:


Kann mir nicht erklären wo der Fehler liegt.

 

Hab bisher noch keine Lösung gefunden.

Hab allerdings ein neues kleine Problemchen, was sicher für dich easy going ist^^

Ich will grad ein Makro machen was einen Bereich "Stunden" ausliest und dann jeweils eine Zelle nach rechts geht und den Wert aus "Stunden"-Bereichszelle auf den Wert in der Zelle rechts daneben addiert.

Also das soll für eine wöchentliche Stundenabrechnung sein, wobei man mit dem Makro dann die Gesamtabrechnung aktualisieren kann.

 

Hallo Stefan,

Du hast einen benannten Bereich namens "Stunden", zB C10:C100
Der Inhalt von C soll jeweils zum Inhalt von D10:100 addiert werden.

Code:

Option Explicit

Sub StundenAufrechnung()

Dim rngStunden As Range, xCell As Range

Set rngStunden = ThisWorkbook.Names("Stunden").RefersToRange.Offset(0, 1)

For Each xCell In rngStunden
        xCell.Value = xCell.Value + xCell.Offset(0, -1).Value
Next
                'löschen der vorhandenen Wochenstunden
    rngStunden.Offset(0, -1).ClearContents

End Sub

Vorsicht > VBA kann gut addieren, es kann auch jeden Mist gut addieren...
> Button gedrückt > Telefon läutet...blablabla > so...jetzt wieder weiter...ach ja...zuerst Button drücken...
Ohne meine letzte Zeile, die die Wochenzahlen löscht, würde ich das Ding nicht einmal zu Hause verwenden...

Auch sonst... sind denn die Wochenzahlen noch irgendwo vorhanden, wenn mal einer Mist gebaut haben sollte, oder einer das Ergebnis anzweifelt? Aus den zwei Spalten könnte man ja nichts rekonstruieren...


Das falsche Ausblenden >
Hast Du die Benannten Bereiche überprüft? Bezeichnen sie die richtigen Zeilen?
Beim Hinzufügen/Entfernen von Zeilen kann man die Bereiche schon einmal verschieben oder gar "vermehren"...
Menü Einfügen > Namen > Definieren >>>
Sind dort gleichnamige Bereiche mehrfach vorhanden (kommt schon vor) ?>>> Alles Sinnlose entfernen.
Sonst kann ich mir dieses Verhalten eigentlich nicht erklären...


Für eine eigene Verwendung hatte ich das Projekt etwas abgespeckt (ich muss ja keiner weisen Obrigkeit gehorchen).
Sinnfreie aber ärgerliche Messageboxen erschlagen und das Minuszeichen beurlaubt (im OnlineShop kreuze ich ja auch nicht an, was ich nicht will).
Dazu eine Gültigkeitsprüfung für die "X" erstellt, damit dort keine dummen Fehler entstehen können.
So wie es jetzt ist, kann man falsch denken wie man will, alles kann Rückgängig gemacht werden.


Die PageBreaks klappen immer noch nicht. Vielleicht spielt da der Druckertreiber mit... der hat hier wohl auch mitzureden, ER muss dann ja die Arbeit machen...

Gruss Urs

 

Jo funktioniert wie gewollt. Naja es ging mir ja nur darum, da ich jede Woche eine Wochenstundenabrechnung abgeben muss, und dann hab ich noch eine Gesamtabrechnung für mich selbst. Und mit dem Makro kann ich nun ganz einfach die Gesamtabrechnung der Stunden mit der jeweiligen Wochenabrechnung aktualisieren und spare mir 50% der Arbeit

Ja das hab ich schon selbst eben eingebaut.

Bereichsname sind kontorliiert worden. Eigentlich stimmt soweit alles. Kann mir das auch gar nicht erklären irgentwie...

Ja ich hätte es auch einfacher gemacht, aber Boss wollte das so haben ^^

Jo das stimmt wohl. ich hatte auch schon überlegt vielleicht provisorisch hinter jeden Abschnitt ein Pagebreak zu setzen, jedoch ist das bei kleineren Abschnitten leider sehr verschwänderisch bzgl. der Blätter.

 

Kann es sein das der Fehler auftritt, weil meine Bereichsnamen aus 2 Buchstaben besteht?Also zBsp AA, AB, AC,....(Alphabet hat nicht gereicht)...hab aber schon "strX = Left(xCell.Offset(0, -1).Value, 2)"

Oder kann der Fehler auftreten, weil in meinem definierten Bereich verbundene Zellen sind?