Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1416to1420
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

Einfügen von Zeilen bei bedingter Formatierung

Einfügen von Zeilen bei bedingter Formatierung
02.04.2015 10:18:38
Zeilen
Hallo,
dies ist mein erster Beitrag in einem Excel-Forum. Bisher konnte ich mir immer irgendwie selbst helfen, nun stehe ich aber vor einem Problem, bei dem ich einfach nicht mehr weiterkomme.
Und zwar habe ich eine freigegebene Excel-Liste, die sehr viele bedingte Formatierungen enthält.
Ein Beispiel:
Regel: „Zellwert < 15“, Wird angewendet auf =$Y:$Y ~f~
Füge ich eine neue Zeile mit Rechtsklick >> Zellen einfügen so verändern sich die bedingten Formatierungen nicht. Sehr gut und so soll es sein.
Leider enthält die Excel-Tabelle sehr viele Einträge und neue Einträge unterscheiden sich teils nur marginal von einer anderen Zeile. Daher neigen die User dazu ganze Zeilen zu kopieren und an gewünschter Stelle einzufügen, z.B. Zeile 27. Aus einer bedingten Formatierungen werden so plötzlich zwei. Eine, die sich ausschließlich auf die Zeile beschränkt und eine für den gesamten Rest. Das sieht dann so aus:
~f~ #1: Regel: „Zellwert < 15“, Wird angewendet auf =$Y$1:$Y$26;$Y$28:$Y$1048576
#2: Regel: „Zellwert < 15“, Wird angewendet auf =$Y$27
Wenn dies nun öfters wiederholt wird, so ist die bedingte Formatierung dementsprechend oft vorhanden und wird extrem unübersichtlich. Außerdem mach ich mir nach gewisser Zeit etwas Sorgen um die Dateigröße bzw. um die allgemeine Funktionalität bei tausenden von bedingten Formatierungen.
Am liebsten würde ich die originale bedingte Formatierung quasi fixieren, so dass diese nicht abgeändert werden kann bzw. sich nicht automatisch verändert.
Kann mir hier evtl. wer weiterhelfen?
Vielen Dank.
Michl.

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Einfügen von Zeilen bei bedingter Formatierung
02.04.2015 11:05:49
Zeilen
Das ist ein "Feature" seit Excel 2007.
Die Dateigröße verändert sich dabei nicht, Excel wird aber total langsam, wenn du diese Datei geöffnet hast (frag mich nicht). Allerdings erst ab 20-30 Formatierungsdoubletten, je nachdem wie komplex sie sind. Wenn es nur um Zellwert >15 in Spalte Y geht, dann wahrscheinlich erst ab 100 Doubletten.
Dagegen kann man nix machen, außer die Formatierungen aller Zeilen außer der ersten löschen und die Formatierung von dort auf die gesamte Tabelle pinseln.
Ich hab mir dafür ein Makro gebaut, weil ich es auch dauernd brauche:
Sub Bedingte_Formatierung_reparieren()
Dim Die1 As Range, Tabellenbreite As Integer
Set Die1 = Range("A2")
Tabellenbreite = Die1.End(xlToRight).Column
Die1.EntireColumn.Select
Selection.Replace What:="", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range(Die1.Offset(1, 0), Die1.Offset(1, 0).End(xlDown).Offset(0, Tabellenbreite - 1)). _
Select
Selection.FormatConditions.Delete
Range(Die1, Die1.Offset(0, Tabellenbreite - 1)).Select
Calculate
Selection.Copy
Range(Die1, Die1.End(xlDown).Offset(0, Tabellenbreite - 1)).Select
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
Die1.EntireColumn.Select
Selection.Replace What:=".", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Die1.Select
End Sub

Bedingungen:
- Tabelle hat in Zeile 1 einen Spaltenkopf
- Tabelle enthält keine Leerspalten.
Leerzeilen werden abgefangen.

Anzeige
AW: Einfügen von Zeilen bei bedingter Formatierung
02.04.2015 11:43:21
Zeilen
Hallo Klexy,
vielen Dank für dein Makro. Ich habe es auch schon erfolgreich testen können. Meine VBA-Kenntnisse sind allerdings, wie man es hier auf der Website auch so schön angeben kann, eher bescheiden. Hier sind mir zu viele Parameter drin, die ich nicht kenne.
Meine bedingten Formatierungen beginnen bei Zeile 7, demnach habe ich A2 durch A7 ersetzt. Dann habe ich versucht dieses Makro bei einer freigegebenen Arbeitsmappe anzuwenden, der Anfang schien zu funktionieren, aber letztendlich endete es mit dem Fehlercode 400 ohne weiterem Text. Wie gesagt sind meine VBA-Kenntnisse nicht sonderlich gut, aber wäre das auch mit einer freigegebenen Arbeitsmappe möglich?
Dennoch nochmals vielen Dank. Ich hatte auch schon speziell nach einem solchen Makro gesucht, aber kein wirklich passendes gefunden.

Anzeige
AW: Einfügen von Zeilen bei bedingter Formatierung
02.04.2015 12:01:49
Zeilen
Lad mal die Tabelle hoch, mit der du es probiert hast (bevor du es probiert hast). Ich schau mal drüber. Das könnte alles mögliche sein. Ich kommentier den Code dann mal etwas ausführlicher.

AW: Einfügen von Zeilen bei bedingter Formatierung
02.04.2015 12:59:14
Zeilen
Hallo,
die Liste anbei. Ich habe allerdings etwas entfernen müssen. Ich hoffe nicht zu viel.
https://www.herber.de/bbs/user/96830.xlsx

AW: Einfügen von Zeilen bei bedingter Formatierung
02.04.2015 15:15:44
Zeilen
Tja, 6 Punkte, warum es nicht geht:
- ich hab mich bei der Definition der Tabellenbreite vertan
Aber auch ohne das hat deine Tabelle ein paar Eigenschaften, die es verhindern und die eine Tabelle nicht haben sollte
- in einer xlsx-Datei kann kein Makro drin sein. Wie hast du das getestet?
- in einer leeren Tabelle funktioniert das nicht, denn es wird der belegte Bereich bereinigt (alles andere wäre ja auch sinnlos)
- in der Tabelle ist eine störende Leerzeile drin (5)
- in der Tabelle ist ein störender Zellverbund drin (6)
- die Tabelle enthält keinen richtigen Tabellenkopf, sondern ein Sammelsurium unterschiedlicher Zellverbünde
https://www.herber.de/bbs/user/96844.xlsm
Den Zellverbund "Aufträge 2014" hab ich gelöscht und links oben die 2014 hingeschrieben. Das sollte genügen für die Leute, die damit zu tun haben. So ein Zellverbund mitten in einer Tabelle geht GAR nicht. Er verhindert z.B. Filterungen, die in Tabellen nicht unüblich sind. Hier im Beispiel verhindert er das Abfangen leerer Zeilen mitten in der Tabelle. Sowas sollte auch nicht sein, kann aber versehentlich passieren und wird deshalb behandelt.
In die graue Leerzeile hab ich jetzt überall je ein "x" reingemacht, damit es wenigstens eine durchgehende Zeile als Orientierung zur Tabellenbreite gibt. Ich weiß, das kann man auch ohne diese durchgehende Kopfzeile machen (UsedRange), aber ich halte es so für besser (ohne da eine Diskussion anstoßen zu wollen).
Was ich nicht verstehe, was aber auch nicht sonderlich stört, ist die Formatierung bis in Zeile 1.048.576. Hast du vor, so viele Einträge zu machen?

Anzeige
warum überhaupt so ...
02.04.2015 15:46:13
der
Hallo ihr beiden,
... und nicht vielleicht ganz simpel?
Ein zusätzliches leeres Tabellenblatt beinhaltet die richtige Formatierung und die überträgt man mit dem Formatpinsel in das verhunzte.
Gruß Werner
.. , - ...

AW: warum überhaupt so ...
02.04.2015 15:51:41
Michl
Hallo Werner,
dein Tipp ist so simpel und wohl doch so effektiv. Ich lasse mir auch das mal durch den Kopf gehen. Vielen Dank dafür.

AW: Einfügen von Zeilen bei bedingter Formatierung
02.04.2015 15:50:49
Zeilen
Hallo,
#1 das Makro steht in meiner Personal.xlsb. Ich hatte eigentlich auch nicht vor, es direkt in die Excelmappe zu packen.
#2 Zum Testen war meine Arbeitsmappe nicht leer. Ich hatte allerdings Realwerte genutzt, die ich nicht hochladen wollte.
#3 In deiner ersten Antwort hast du geschrieben "Leerzeilen werden abgefangen.". Daraus schloss ich, dass eine Leerzeile keine Probleme darstellt. Im Test sah es auch so aus, als hätte das Makro trotz Leerzeilen problemlos funktioniert, ich werde das aber noch mal genau testen.
#4 Der Zellverbund war vor meinem ersten Eintrag, ich wollte ja erst ab A7 das Makro anwenden, also nach dem Zellverbund. Der blieb davon auch unangetastet und hat so weit funktioniert.
#5 Der Tabellenkopf ist zwar nicht der ganz klassische, aber ich sehe da kein Problem.
Und zur bedingten Formatierung bis 1.048.576:
Ursprünglich habe ich die bedingte Formatierung über ganze Spalten bzw. Bereiche erstellt. Eben z.B. Y:Y. Da ich die bedingte Formatierung im Kopf aber nicht wollte, habe ich diese aus den ersten paar Zeilen gelöscht. Excel hat dann selbstständig eine bedingte Formatierung von Y7:Y1048576 erstellt. Die Anzahl der benötigten Zeilen wären allerdings nur ca. 5.000.
Letztendlich hast du mir mit dem Makro sehr geholfen. Dank deinen Kommentaren versteh ich auch den Teil mit den Punkten, die dann wieder entfernt werden. ;-)

Anzeige
AW: Einfügen von Zeilen bei bedingter Formatierung
02.04.2015 16:41:36
Zeilen
Personal.xlsb ist genehmigt ;-)
Realwerte kann man ja verfremden, indem man aus allen a ein e, aus allen 2 eine 3 usw. macht. Der Charakter der Daten bleibt dadurch erhalten ohne zuviel zu verraten.
Das Makro hat nur zufällig funktioniert, weil in der Zeile 7 wahrscheinlich jede Zelle belegt war. Ich hatte die Tabellenbreite versehentlich in der ersten Datenzeile abgeprüft und nicht in der Kopfzeile (die ja normalerweise eins drüber ist). Mein Fehler.
So ein Tabellenkopf kann u.U. ein Problem werden, wenn du die Liste sortieren willst.
Eine durchgehende Leerzeile unter dem Kopf führt fast sicher zu Problemen beim Sortieren, weil du u.U. nur einige Spalten durchsortierst und die anderen bleiben, wie sie sind. Damit sind die Datensätze zerrissen und du bemerkst es vielleicht gar nicht.
Die Lösung von Werner ist natürlich auch nicht ohne. Funktioniert aber nur, wenn die Formatierungen immer bis eine Million gehen und die Nutzdatei nicht länger wird als die Formatierungsreserve.
Das kann aber schnell in die Dateigröße gehen, wenn man nicht nur bedingte Formatierungen, sondern auch Farben und Schriftgrößen usw. hat.
Ich formatiere nur den genutzten Bereich und hab sehr viele entsprechend gefährdete Listen. Daher ist für mich das Makro ökonomischer.

Anzeige
leider, leider ...
02.04.2015 11:11:43
der
Hallo Michi,
... das von Dir geschilderte ist wirklich ein echtes Übel. Auch mich stört es (momentan noch) und ich hoffe, dass sich eines Tages die MS-Programmierer- mal einer echten Lösung dieser Problematik an-/vornehmen.
Momentan hilft da mE nur (mit noch vertretbaren Aufwand, wenn man nicht selbst programmieren will) den Usern die Problematik erklären und diese hin-/anweisen und auch auch immer mal wieder erinnern, daß sie immer nur (leere) Zeilen einfügen und anschließend nur Werte kopieren soll(t)en. Ansonsten mußt Du wohl ab und zu mal "Säubern" (verschiedene Möglichkeiten) um das ganze nicht ausufern zu lassen.
Gruß Werner
.. , - ...

Anzeige
AW: Einfügen von Zeilen bei bedingter Formatierung
02.04.2015 16:51:34
Zeilen
Ich stell das Makro hier in etwas allgemeingültigerer und kommentierter Form nochmal rein.
Vielleicht kann es ja der eine oder andere gebrauchen. Mir hilft es täglich.
Sub Bedingte_Formatierung_reparieren()
Dim ObenLinks As Range, Tabellenbreite As Integer
'erste Zelle im formatierten Bereich (normalerweise A2)
Set ObenLinks = Range("A2")
'Das muss an einer durchgehenden Zeile, normalerweise der Tabellenkopf in Zeile 1 gemacht  _
werden.
Tabellenbreite = ObenLinks.Offset(-1, 0).End(xlToRight).Column
'das fängt Leerzeilen mitten in der Tabelle ab, indem ein Punkt eingefügt wird, wenn leer
ObenLinks.EntireColumn.Select
Selection.Replace What:="", Replacement:=".", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'bedingte Formatierungen überall außer in der ersten Zeile löschen
Range(ObenLinks.Offset(1, 0), ObenLinks.Offset(1, 0).End(xlDown).Offset(0, Tabellenbreite -  _
1)).Select
Selection.FormatConditions.Delete
'bedingte Formatierungen aus der ersten Zeile kopieren
Range(ObenLinks, ObenLinks.Offset(0, Tabellenbreite - 1)).Select
Calculate
Selection.Copy
'bedingte Formatierungen auf die ganze Tabelle verschieben
Range(ObenLinks, ObenLinks.End(xlDown).Offset(0, Tabellenbreite - 1)).Select
Selection.PasteSpecial Paste:=xlPasteFormats
Application.CutCopyMode = False
'löscht die Punkte von vorhin wieder
ObenLinks.EntireColumn.Select
Selection.Replace What:=".", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
ObenLinks.Select
End Sub

Anzeige

139 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige