Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: VBA Aktive Zeile Inhalt außer Formeln löschen

VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 09:08:22
Peer
Hallo liebe Excel/VBA Gemeinde.

Ich habe in meinem langjährigen Projekt, dass ich mit viel Unterstützung aus diesem Forum zum Laufen bekommen habe festgestellt, das sich doch ein Fehler, der jetzt erst aufgefallen ist, zu erkennen gegeben hat.
Ich habe allen Kollegen, die diese Datei nutzen, die benutzerfreundliche Möglichkeit gegeben, die ausgewählte Zeile mit ihren Daten zu löschen. Dies habe ich über das Kontext-Menü "Arbeitstag löschen" realisiert. Beim Löschen sollten aber alle Formeln erhalten bleiben, da sie Grundlage von vielen Berechnungen sind.
Ich habe alle Zellen, die Formeln enthalten "gesperrt" und einen Blattschutz eingerichtet.

Für das Kontext Menü habe ich folgende Prozedur im Modul mod_AktiveRowClearContents hinterlegt...
Sub Inhalt_loeschen()

' Löscht den Inhalt der gewählten Zeile (Formeln und Formatierungen bleiben erhalten)
Dim lngColumn As Long, Datum As Date

Datum = CDate(Cells(ActiveCell.Row, 2))

If MsgBox("Möchtest du den " & Datum & " löschen?", vbQuestion + vbYesNo, "Arbeitstag löschen") = vbYes Then
ActiveSheet.Unprotect
' ab Spalte 4 bis zur letzten Spalte mit Inhalten
For lngColumn = 4 To Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
If Not Cells(ActiveCell.Row, lngColumn).HasFormula Then 'wenn in Zeile Spalte KEINE Formeln dann gehe zu 1
If Cells(ActiveCell.Row, lngColumn).MergeCells Then 'wenn in Zeile Spalte Verbundene Zellen erhält dann ...
Cells(ActiveCell.Row, lngColumn).MergeArea.ClearContents '... lösche deren Inhalt, sonst 2
Else
Call Cells(ActiveCell.Row, lngColumn).ClearContents ' 2 - lösche den Inhalt der Spalte der aktiven Zeile
Cells(ActiveCell.Row, 22) = "00:00"
End If
End If
Next ' 1 - gehe zur nächsten Spalte

ActiveSheet.Protect
Else: Exit Sub
End If

End Sub
Leider werden die Formeln speziell in den Spalten N, W und BI gelöscht, die aber nicht gelöscht werden sollten.
Nun habe ich ebenfalls die Möglichkeit über das Kontext Menü den Arbeitstag mit "Arbeitstag eintragen" (frm_Tag) einzutragen. Ich habe das Eingeben des Arbeitstages nur über die UF frm_Tag zugelassen, um durch mein unzureichendes VBA Verständnis Eingaben zu verhindern, die zu Fehler führen könnten.

Ich schicke die sehr stark reduzierte Datei, um euch das Anliegen zu veranschaulichen. Die enthaltenen Daten sind größtenteils fiktiv.
https://www.herber.de/bbs/user/172414.xlsm

Wie kann ich verhindern, das die Formeln gelöscht werden?
Da mein VBA nicht so gut ist, um es zu aus dem Stand zu verstehen und umzusetzen, wäre ich wieder auf eure Hilfe angewiesen.

Vielen Dank im Voraus.
VG Peer
Anzeige

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 10:02:45
Yal
Hallo Peer,

der grösste Spass dabei ist, zuerst rauszufinden, wo diese "Inhalt_löschen" abgelegt ist.

Wenn ich das richtig sehe, sind nur Zellen in den Spalten D:E, N:O, BI:BJ verbunden. Ein Grund, warum diese Zelle verbunden sind, könnte ich nicht erkennen. Mein Tipp: vermeide verbundene Zellen.

Ohne sieht dein Code so aus:
Sub Inhalt_loeschen()

' Löscht den Inhalt der gewählten Zeile (Formeln und Formatierungen bleiben erhalten)
With ActiveCell.EntireRow
If MsgBox("Möchtest du den " & CDate(.Cells(2)) & " löschen?", vbQuestion + vbYesNo, "Arbeitstag löschen") = vbYes Then
ActiveSheet.Unprotect
.Range("D1:XFD1").SpecialCells(xlCellTypeConstants).ClearContents 'relative Adressierung innerhalb der ActiveCell.EntireRow
ActiveSheet.Protect
End If
End With
End Sub


VG
Yal
Anzeige
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 11:43:26
GerdL
Moin Peer,

probiere es mal nach diesem Schema.
Dim C As Range


For Each C In Range(Cells(ActiveCell.Row, 4), Cells(ActiveCell.Row, 62)).Cells
If C.MergeCells = True Then
If Not C.HasFormula Then C.MergeArea.ClearContents
Else
If Not C.HasFormula Then C.ClearContents
End If
Next

Gruß Gerd
Anzeige
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 12:49:47
Peer
Hallo Gerd.

Auch deine Version funktioniert fast perfekt.
Leider wird aber die Formel in Spalte BI:BJ gelöscht und in BK nicht, statt andersherum.

Verstehe ich nicht, denn du hast doch den Bereich bis Spalte 62 einbezogen!?

Gruß Peer
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 11:54:59
daniel
Hi
das Problem ist, dass deine einfache Zählschleife nicht nur über die erste Zelle eines Verbundes läuft, sondern auch über die zweit- und drittzelle.
diese enthalten die Formel natürlich nicht, aber dann löschst du den ganzen Zellverbund (.mergearea.ClearContents)

die einfachste Lösung: lösche die Inhalte nicht mit .ClearContents, sondern einfach mit .Value = ""
dann musst du dich nicht drum kümmern, ob du eine Verbundzelle hast oder nicht:

und so nebenbei: das Rücksetzen der Uhrzeit muss nur einmal gemacht werden, es gehört daher nicht die Schleife über die Spalten

Sub Inhalt_loeschen()

' Löscht den Inhalt der gewählten Zeile (Formeln und Formatierungen bleiben erhalten)
Dim lngColumn As Long, Datum As Date

Datum = CDate(Cells(ActiveCell.Row, 2))

If MsgBox("Möchtest du den " & Datum & " löschen?", vbQuestion + vbYesNo, "Arbeitstag löschen") = vbYes Then
ActiveSheet.Unprotect
' ab Spalte 4 bis zur letzten Spalte mit Inhalten
For lngColumn = 4 To Cells(ActiveCell.Row, Columns.Count).End(xlToLeft).Column
If Not Cells(ActiveCell.Row, lngColumn).HasFormula Then 'wenn in Zeile Spalte KEINE Formeln dann gehe zu 1
Cells(ActiveCell.Row, lngColumn).value = ""
End If
Next ' 1 - gehe zur nächsten Spalte
Cells(ActiveCell.Row, 22) = "00:00"
ActiveSheet.Protect
Else: Exit Sub
End If

End Sub


wenn du willst, könntest du die zweit- und drittzellen eines Verbundes auch überspringen, weil die ja sowieso leer sind.

dazu müsseste du vor dem NEXT den Schleifenzähler, der ja die Spaltennummer ist, vorstellen:
    ...

lngColumn = lngColumn + Cells(ActiveCell.row, .lngColumn).MergeArea.Columns.Count - 1
next
Anzeige
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 12:45:08
Peer
Hallo daniel.

Auch dir einen herzlichen Dank für deine Hilfe.
Dein Beispiel, das ich bei mir gleich eingefügt habe, funktioniert soweit auch gut.
Nur ein Problem scheint entstanden zu sein.

Nun wird aber leider die Uhrzeit immer auf '0' gesetzt, selbst wenn ich bei 'Arbeitstag eingeben' die Arbeitszeit im UF frm_Tag im Textfeld txt_ArbZeit beim Schließen der UF nicht in die jeweilige Zelle übertragen wird, da die Formel in Spalte V gelöscht wird. Warum das? Die Spalte V ist auch gesperrt!

VG Peer
Anzeige
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 12:47:32
daniel
dann schiebe das zurücksetzen der Uhrzzeit wieder dahin, wo es vorher war.
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 13:11:27
Peer
Es ist dann kein Unterschied, weil die Spalte V, wo die Zeit eingetragen wird, mit seiner Formel gelöscht wird.
Keine Ahnung, warum? Denn mit deinem Beispiel sollte es ja nicht sein.

VG
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 13:18:17
daniel
dass die Spalte V mit dem Wert "00:00" überschrieben wird, steht in deinem Ursprungscode Code schon drin.
daher das habe ich so von dir übernommen ohne weiter drüber nach zudenken.
dann lösch es halt. ist dein Code.
Gruß Daniel
Anzeige
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 13:28:17
Peer
Stimmt, daniel.

Ich Trottel überschreibe ja mit meinen Wert die Formel.
Danke für den Hinweis.

VG
Peer
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 10:42:33
Peer
Hallo Yal.

Vielen Dank für deine schnelle und hilfreiche Antwort.

Ich weiß, das Thema verbundene Zellen ist keine gute Masche. Leider hat der Ursprung der Datei diese Zellen schon besessen und ich habe damit weitergearbeitet, ohne zu ahnen, was es nachträglich für Probleme führen kann, gerade bei VBA. Damals war alles noch ohne VBA.
Ich möchte durchaus die verbundenen Zellen durch 'große' Zellen ersetzen, aber leider fehlt mir momentan die Zeit, alles so umzustellen, das es nicht Probleme bereitet, bis ich alle VBA Proceduren und Links angepasst habe.
Derzeit suche ich eine Lösung, wie ich die verbundenen Zellen mit einbinde z.B. in deinem Code, der vielversprechend aussieht.

VG
Peer
Anzeige
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 11:46:05
Peer
Ich habe deinen Code genutzt und probiert.
Er funktioniert schon ganz gut. Auch die verbundenen Zellen machen (noch) keine Probleme.
Leider sind zwei Fehler aufgetaucht.

Aus unerklärlichen Gründen wird bei Rechtsklick auf den Kalendertag (Spalte B) beim Aufruf des Kontext Menüs und "Arbeitstag löschen" ab und zu das Sub aus dem Worksheet_SelectionChange-Event aufgerufen. Warum, kann ich mir nicht erklären.

Des weiteren wird die jeweilige Zeile, die noch leer ist und trotzdem gelöscht werden soll, mit dem Laufzeitfehler 1004 bemängelt. Hier habe ich (erstmal) den Fehler abgefangen...
On Error GoTo ErrorHandler


With ActiveCell.EntireRow
If MsgBox("Möchtest du den " & CDate(.Cells(2)) & " löschen?", vbQuestion + vbYesNo, "Arbeitstag löschen") = vbYes Then
ActiveSheet.Unprotect
.Range("D1:XFD1").SpecialCells(xlCellTypeConstants).ClearContents 'relative Adressierung innerhalb der ActiveCell.EntireRow
.Cells(22) = "00:00"
ActiveSheet.Protect
End If
End With

ErrorHandler:
If Err.Number = 1004 Then
MsgBox "Der Tag ist schon leer", vbOKOnly, "Löschen nicht nötig"
Exit Sub
End If

Für mich als schlechter VBA-Nutzer die Frage, ob ich es so richtig gemacht habe oder es eine vernünftigere bzw. bessere Lösung gibt?

Gruß Peer
Anzeige
AW: VBA Aktive Zeile Inhalt außer Formeln löschen
26.09.2024 13:45:48
Peer
Vielen Dank an alle Helfer.
Ich habe alle drei Varianten für mich durchprobiert und schlussendlich für daniel sein Beispiel entschieden, weil damit meine gemachten Fehler sich weniger gemeldet haben.
Trotzdem behalte ich die anderen beiden Beispiele, um dann auch diese Möglichkeiten in Betracht zu ziehen, wenn ich die verbundenen Zellen umgestellt habe.

VG
Peer
Anzeige
;

Forumthreads zu verwandten Themen

Anzeige
Anzeige
Anzeige
Anzeige
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige