Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1280to1284
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Werte löschen, Formeln behalten, verbundene Zellen

Werte löschen, Formeln behalten, verbundene Zellen
02.10.2012 18:07:08
Ina
Hallo zusammen im Forum,
ich möchte in meiner Tabelle in den Zellen C5:F5,J5:K5,E10:J40 Werte löschen, die Formeln aber erhalten.
Das Problem dabei ist, das einige Zellen verbunden sind und der Codeschnipsel (s. unten) so leider nicht funktioniert.
Wer kann mir helfen und den Code entsprechend "ummodeln"?
Danke für eure Hilfe! LG Ina
Sub loeschen()
Dim raZelle As Range
For Each raZelle In ActiveSheet.Range("C5:F5,J5:K5,E10:J40")
If Not raZelle.HasFormula Then raZelle.ClearContents
Next raZelle
End Sub

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Die 1.Abfrage im For-Zyklus muss dann ...
02.10.2012 19:03:34
Luc:-?
…natürlich eine andere sein, Ina:
If raZelle.MergeCells Then
If Not raZelle(1).HasFormula Then raZelle(1).ClearContents
ElseIf Not raZelle.HasFormula Then
raZelle.ClearContents
End If
Wenn du aber alle bzw mehrere Einzelzellen der VbZelle mit Werten belegt hättest und du diese anzeigen lassen wolltest, müsstest du einen Zyklus über alle Teilzellen laufen lassen. Zum Löschen sollte aber das oben Angegebene reichen → alle Teilzellen sind dann gelöscht (ggf auch mit raZelle.ClearContents bzw raZelle.MergeArea.ClearContents → mal selber ausprobieren!).
Gruß Luc :-?
PS: Es ist übrigens kaum bekannt, dass eine For Each-Laufvariable auch ein Datenfeld bzw mehrzelliger Bereich sein kann; und genau Letzteres wäre bei VbZellen der Fall. Von einem ganzen Bereich kann man in VBA aber keine singuläre Eigenschaft abfragen → der müsste dafür elementweise durchlaufen wdn. Übrigens kommt daher wohl auch die Auffassung, dass VbZellen nur Probleme bereiten. Die das propagieren durchschauen nur nicht die dahinter stehenden Mechanismen. Man kann diesen Effekt übrigens auch ganz ohne VbZellen erzielen. Ein Variant der aus Variant-Datenfeldern besteht, reagiert auch so.

Anzeige
AW: Die 1.Abfrage im For-Zyklus muss dann ...
02.10.2012 19:39:48
Ina
Hallo,
leider sind meine jetzt Formeln weg (zum Glück nur in der Testmappe)...
LG Ina

Wer verbundene Zellen nutzt, fällt selbst hinein
02.10.2012 19:25:06
Erich
Hi,
mein Vorschlag:

Option Explicit
Sub loeschen()
Dim raZelle As Range
For Each raZelle In ActiveSheet.Range("C5:F5,J5:K5,E10:J40")
If raZelle.HasFormula Then
If raZelle.MergeCells Then
If raZelle.Address = raZelle.MergeArea(1).Address Then _
raZelle.MergeArea.ClearContents
Else
raZelle.ClearContents
End If
End If
Next raZelle
End Sub
@Luc:
Hast du deinen Vorschlag mal getestet - insbesondere die folgende Zeile?
raZelle(1).ClearContents
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Wer verbundene Zellen nutzt, fällt selbst hinein
02.10.2012 19:40:16
Ina
Hallo,
leider sind meine jetzt Formeln weg (zum Glück nur in der Testmappe)...
LG Ina

Stimmt, bei senkrechter Anordnung wird ...
02.10.2012 22:06:26
Luc:-?
…trotzdem die 2.Zelle gewählt, Erich,
denn raZelle(1) ist ja immer zutreffend. Und da die meist leer ist, wird sie und die andere gleich mit gelöscht. Wenn in beiden TeilZellen eine Fml stünde, würde das nicht passieren. Hier muss also außerdem noch auf leer geprüft wdn oder im Standardfall auf raZelle.MergeArea.HasFormula ausgewichen wdn (die FmlAnzeige würde aber trotzdem nur/auch mit raZelle(1) laufen). Bei waagerechtem Verbund würde das aber wie genannt fktionieren, wenn senkrecht abgearbeitet wird, denn die 2.Zelle hat dann ja einen anderen Index und wird also gar nicht überprüft.
Deine Version läuft wohl durch, weil die andere Zelle leer ist (wohl 'ne Prioritätsregelung), anderenfalls wäre das Ergebnis der Prüfung (bis xl11) Null.
Aber inzwischen habt ihr's ja hinbekommen. ;-)
Gruß Luc :-?

Anzeige
AW: Werte löschen, Formeln behalten, verbundene Zellen
02.10.2012 19:51:06
Josef

Hallo Ina,
probiers mal so.
Sub loeschen()
  Dim rng As Range
  
  On Error Resume Next
  Set rng = ActiveSheet.Range("C5:F5,J5:K5,E10:J40").SpecialCells(xlCellTypeConstants)
  On Error GoTo 0
  
  If Not rng Is Nothing Then rng = ""
  
  Set rng = Nothing
  
End Sub




« Gruß Sepp »

Anzeige
Spielverderber ;-)
02.10.2012 20:19:49
Erich
Hi Sepp,
keine Schleife, kein Ärger mit verbundenen Zellen - wo bleibt denn da der Spaß?
Hut ab und Grüße aus Kamp-Lintfort von Erich
P.S. @Ina: Es ist doch wohl nicht so schwer, testhalber ein paar Formeln in die Testmappe zu schreiben, oder?

AW: Spielverderber ;-)
02.10.2012 20:26:03
Josef

Hallo Erich,
sorry, wollte dir nicht den Spaß verderben;-)))
brauchst aber nur ="" gegen .ClearContents austauschen, dann kracht's schon wieder :-)
Grüße zurück aus Tirol - Sepp

Anzeige
AW: Spielverderber ;-)
02.10.2012 20:49:11
Erich
Hi Sepp,
mmmh, bei mir kracht's nicht mal mit .ClearContents. :-(
Zu rng gehört immer die ganze MergeArea.
Aber ="" oder .ClearContents haben eh den selben Effekt.
Was ich merkwürdig finde:
Nach Cells(1,1)="" ist ISTLEER(A1) wahr, ISTTEXT(A1) falsch.
Da steht demnach kein String der Länge 0 drin. Ist m. E. nicht so ganz nach der reinen Lehre.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

AW: Spielverderber ;-)
02.10.2012 20:42:41
Ina
Hallo Sepp,
danke für Deinen Beitrag, aber da passt irgendetwas nicht...
Ich habe einfach mal eine Mappe vorbereitet. Sie ist geschützt (ohne Passwort).
Durch das Makro wird praktisch nichts gelöscht...
https://www.herber.de/bbs/user/81962.xls
LG Ina

Anzeige
AW: Spielverderber ;-)
02.10.2012 21:31:44
Josef

Hallo Ina,
klar, mit Blattschutz geht das nicht.
Sub loeschen()
  Dim rng As Range
  
  With ActiveSheet
    .Unprotect
    On Error Resume Next
    Set rng = .Range("C4:F4,J4:K4,E10:J40").SpecialCells(xlCellTypeConstants)
    On Error GoTo 0
    
    If Not rng Is Nothing Then rng = ""
    .Protect
  End With
  
  Set rng = Nothing
End Sub



« Gruß Sepp »

Anzeige
AW: Spielverderber ;-) Makro klappt noch nicht
02.10.2012 21:42:33
Ina
Hallo Sepp,
das Makro löscht neben den gewünschten Bereichen, leider auch den Bereich zwischen C4 und J9.
Die Mappe war übrigends geschützt, nicht jedoch die zu löschenden Bereiche C4:F4,J4:K4,E10:J40.
Irgendetwas läuft da noch falsch...
LG Ina

mit Intersect klappt es
03.10.2012 09:54:25
Erich
Hi Ina und Sepp,
ja, da geben die SpecialCells auch Bereiche zurück, die gar nicht zum betrachteten Betreich gehören.
In der Mappe https://www.herber.de/bbs/user/81967.xls
werden beim Klick auf SpecCells drei Zellen in Spalte A beschrieben:
- A1 ist der betrachtete Betreich
- A3 sind die SpezialCells(xlCellTypeConstants) davon
- A5 ist der Intersect aus A1 und A3
In der Liste in A3 taucht erst die Zelle A1 auf, beim wiederholten Lauf auch A3 und A5.
SpezialCells(xlCellTypeConstants) kümmert sich also nicht zuverlässig darum, welcher Range davor steht.
loeschen3() sollte jetzt funktionieren, loeschen2() auch. Hier die Codes:

Option Explicit
Sub SpecCells()
Dim strR As String, rngSC As Range, rngIn As Range
strR = "C4:F4,J4:K4,E10:J40"
Cells(1, 1) = strR
Set rngSC = ActiveSheet.Range(strR).specialCells(xlCellTypeConstants)
Cells(3, 1) = rngSC.Address(0, 0)
Set rngIn = Intersect(rngSC, ActiveSheet.Range(strR))
If Not rngIn Is Nothing Then Cells(5, 1) = rngIn.Address(0, 0) Else Cells(5, 1) = ""
End Sub
Sub loeschen3() 'von Sepp Ehrensberger - klappt mit Intersect
Dim rngA As Range, rngL As Range
With ActiveSheet
Set rngA = .Range("C4:F4,J4:K4,E10:J40")
.Unprotect
On Error Resume Next
Set rngL = rngA.specialCells(xlCellTypeConstants)
On Error GoTo 0
If Not rngL Is Nothing Then
Set rngL = Intersect(rngA, rngL)
If Not rngL Is Nothing Then rngL = ""
End If
.Protect
End With
Set rngA = Nothing
Set rngL = Nothing
End Sub
Sub loeschen2() 'von Erich - klappt löscht Werte, keine Formeln
Dim raZelle As Range
For Each raZelle In ActiveSheet.Range("C4:F4,J4:K4,E10:J40")
If Not (IsEmpty(raZelle) Or raZelle.HasFormula) Then
If raZelle.MergeCells Then
If raZelle.Address = raZelle.MergeArea(1).Address Then _
raZelle.MergeArea.ClearContents
Else
raZelle.ClearContents
End If
End If
Next raZelle
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich und: einen schönen Feiertag!

Anzeige
Für Erich...
02.10.2012 20:48:51
Ina
Hallo Erich,
danke für Deinen Beitrag, aber das NMakro funktioniert genau falsch herum...
Es löscht die Formeln, nicht jedoch die Werte :-(
Sub loeschen()
Dim raZelle As Range
For Each raZelle In ActiveSheet.Range("C5:F5,J5:K5,E10:J40")
If raZelle.HasFormula Then
If raZelle.MergeCells Then
If raZelle.Address = raZelle.MergeArea(1).Address Then _
raZelle.MergeArea.ClearContents
Else
raZelle.ClearContents
End If
End If
Next raZelle
End Sub

LG Ina

AW: Für Erich...
02.10.2012 21:05:02
Erich
Hi Ina,
auch wenn ich meine, dass du Sepps bessere Lösung nehmen solltest,
hier eine neue mit der MergeArea:

Sub loeschen()
Dim raZelle As Range
For Each raZelle In ActiveSheet.Range("C5:F5,J5:K5,E10:J40")
If Not (IsEmpty(raZelle) Or raZelle.HasFormula) Then
If raZelle.MergeCells Then
If raZelle.Address = raZelle.MergeArea(1).Address Then _
raZelle.MergeArea.ClearContents
Else
raZelle.ClearContents
End If
End If
Next raZelle
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Für Erich...
02.10.2012 21:27:19
Ina
Hallo Erich,
Dein Makro läuft jetzt genau sop, wie ich es benötige.
Herzlichen Dank für die Mühe!
Das Makro von Sepp löscht leider nicht nur die gewünschten Bereiche sondern auch weitere und funktioniert nicht, wenn das Tabellenblatt geschützt ist (zu löschende Bereiche natürlich ausgenommen).
Hier ist die Mappe zur Ansicht https://www.herber.de/bbs/user/81963.xls
LG Ina

114 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige