Herbers Excel-Forum - das Archiv
Zeile automatisch hoch kopieren

Betrifft: Zeile automatisch hoch kopieren
von: Thomas
Geschrieben am: 11.09.2019 19:09:53
Hallo Liebe Excel-Gemeinde
Ich möchte gerne in der angehängten Tabelle, leere / gelöschte Zeilen automatisch bis unter die nächste volle Zeile kopieren/verschieben, ohne das die Tabelle dabei kleiner wird.
Soll heißen das die Zeilenanzahl (1-20)gleich bleibt.
Schön wäre das ganze sogar seitenübergreifend.
Ist das überhaupt so möglich? Wer könnte dabei helfen?
https://www.herber.de/bbs/user/131997.xlsx
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1712681.html
Geschrieben am: 12.09.2019 13:22:57
Hallo Thomas,
folgende müsste aufgrund deiner Beschreibung eigentlich passen:
Sub LeerzeilenOberhalbKicken()
Dim r As Long, c As Long, Calc As Long
If (ActiveWorkbook Is Nothing) Then Exit Sub
Application.ScreenUpdating = False
Calc = Application.Calculation
Application.Calculation = xlCalculationManual
r = ActiveCell.Row: c = ActiveCell.Column
While (r > 8)
If Trim(Cells(r, c)) = "" Then Cells(r, 1).EntireRow.Delete
r = r - 1
Wend
Cells(1, 1).Select
Application.Calculation = Calc
Application.ScreenUpdating = True
End Sub
Positioniere den Mauszeiger irgendwo unterhalb des Bereichs, in dem inhaltsleere Zeilen gelöscht werden sollen (Spalte A scheint geeignet)und starte das Makro.
vg, MM
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1712690.html
Geschrieben am: 12.09.2019 14:26:45
Hallo Thomas,
ich habe mal eine Lösung zu Deinem Problem erarbeitet. Dabei musste ich jedoch einige kleinere Veränderungen an Deiner Tabelle vornehmen. So habe ich die Formeln in den Spalten "F", "H", "J", "L", usw. gelöscht und die Formeln in der Spalte "AI" geändert. Die Ergebnisse sind natürlich dieselben wie zuvor. Die Spalten "AS" bis "AX" kannst Du für den normalen Betrieb ausblenden. Sollte sich die Blocklänge verändern, kannst Du in den blau unterlegten Feldern die Formeln (an deren Ende) durch die jetzt neue letzte Zeile abändern. In den gelb unterlegten Feldern einfach die neuen Bereiche eintragen.
Weitere Änderungen müssen evtl. in dem Makro 1 getätigt werden. Derselbe Makro kann für weitere Arbeitsblätter verwendet werden. Ebenso die von mir eingerichtete Schaltfläche. Ich gehe dabei davon aus, dass der Makro 1 stets mit der Schaltfläche des (einen) geöffneten Arbeitsblattes ausgeführt wird.
Hier meine Datei:
https://www.herber.de/bbs/user/132006.xlsm
Bitte mal ausprobieren; Rückmeldung wäre schön.
Mit freundlichem Gruß
Peter Kloßek
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1712749.html
Geschrieben am: 12.09.2019 18:20:17
Hallo Peter
Erst mal vielen Dank für Deine Mühe.
Es macht den Anschein, so auf die Schnelle, das es so funzt wie ich mir das vorstelle.
Mit der Formelumstellung ist auch genial, hätte man auch selber drauf kommen können.
Werde jetzt erst mal versuchen, das nachzuvollziehen was Du da programmiert hast und es in Ruhe ausprobieren.
Aber das man jetzt alle Blätter/Tabellen aufschieben kann, das geht mit dem Makro nicht, richtig?
Wie du vllt. erkannt hast, fehlen einige Tabellen.(zwischen Best.2 und Best.8).
Wenn alle Tabellen einmal ausgefüllt sind, ist es ja so das immer wieder Leerzeilen entstehen, weil Namen wegfallen. Dann sollen im Prinzip alle Leerzeile aufrücken.
Nochmal vielen Dank.
Das hätte ich nicht hinbekommen.
Viele Grüße Thomas
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1712769.html
Geschrieben am: 12.09.2019 19:38:37
Noch ein kleiner Nachtrag.
Wenn in Zeile 20 etwas steht funzt es nicht mehr. Diese Zeile schiebt das Makro dann nicht mit hoch...
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1712782.html
Geschrieben am: 12.09.2019 22:45:58
Hallo Peter
Also mit der letzten Zeile (20), das habe ich hinbekommen.
So ganz bin ich noch nicht hinter dieses Makro gestoßen, dafür fehlen mir einfach die Kenntnisse.
Ich habe zwar auch mal versucht das Ganze tabellenübergreifend hinzubekommen, jedoch vergebens.
Wäre es eventuell sinnvoller gewesen die einzelnen Tabellenblätter untereinander, in einer Tabelle aufzuführen?
VG Thomas
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1712784.html
Geschrieben am: 13.09.2019 00:52:34
Hallo Thomas,
schönen Dank für Deinen Hinweis zur Zeile 20. Ich hatte stundenlang getestet - aber nie die letzte Zeile belegt! Die Lösung ist ganz einfach: gebe in die Zelle "AT28" die Zahl "28" ein.
Man kann auch mit mehr als 20 Zeilen in derselben Tabelle arbeiten. Dazu einfach die Tabelle verlängern, einschließlich der Spalte "AT"; hier kann man die vorletzte Eintragung (Formel) herunterkopieren und in der letzten Zeile wiederum die letzte Zeilennummer eintragen. Bitte dann auch die entsprechenden Hinweise im Makro selbst beachten!
Das Verfahren ist für jedes Bestell-Blatt dasselbe. Es müssen dazu natürlich die Spalten "AS" bis "AX" eingefügt werden. Bitte auch die entsprechenden Formeländerungen vornehmen. Auch den Makro-Button kannst Du ganz einfach auf die anderen Bestell-Blätter kopieren. Markiere einfach die Zellen "AM2" bis "AR2", kopiere und füge in die anderen Blätter ein. Es wird immer der Makro 1 ausgeführt und die Auswirkung bezieht sich immer auf das jeweils geöffnete Blatt (daher nie mehrere Blätter gleichzeitig öffnen!).
Möchtest Du sämtliche Blätter in einem Rutsch bearbeiten, dann geht dies mit einem Vorlaufmakro:
Sub Makro99()
sheets("Best.1").select
call Makro1
sheets("Best.2").select
call Makro1
sheets("Best...).select
call Makro1 (usw.)
End Sub
Richte dann einen neuen Makrobutton "In allen Blättern Leerzeilen entfernen" ein und ordne diesem den Makro 99 zu.
Das Ganze habe ich natürlich nicht getestet, müsste aber funktionieren. Falls noch Fragen offen sind, bitte nochmals melden.
Mit freundlichem Gruß
Peter Kloßek
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1712991.html
Geschrieben am: 13.09.2019 18:16:15
Hallo Thomas,
ich habe nochmal gründlich getestet - und leider noch einen logischen Fehler im Programm gefunden! Natürlich, heute ist ja "Freitag, der 13.". In Tabelle 1 der beiliegenden Datei findest Du eine Beschreibung der Formeln und des Makros 1. Hier ist auch gekennzeichnet, welcher Teil des Makros von der unteren Position (rot gedruckt) nach der oberen Position (blau gedruckt) verschoben werden muss. In der Tabelle 2 habe ich den vollständigen berichtigten Makro 1 dargestellt.
Hier die Erläuterungen:
https://www.herber.de/bbs/user/132034.xlsm
Die ganze Sache ist leider etwas kompliziert, weil nach jedem hochkopieren wieder eine andere Struktur der Daten entsteht. Bitte führe die Berichtigung durch und teste nochmal ausgiebig. Wenn weitere Fragen auftauchen sollten, stehe ich gern zur Verfügung.
Mit freundlichem Gruß
Peter Kloßek
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1713004.html
Geschrieben am: 13.09.2019 21:04:59
Hallo Peter
Das Makro1 habe ich geändert, allerdings hat sich nichts geändert, meine ich.
In die Zeile AT28 habe ich 29 geschrieben, dann hat es gefunzt.
28 stand ja schon in AT27.
Das mit Makro99 habe ich auch getestet, funzt auch.
Jetzt habe ich nur ein Problem, das ja dann bei allen Tabellen die Leerzeilen raus sind, nun entstehen mir ja dann bei den Etiketten, da diese ja fortlaufend sind, "Leeretiketten".
Idealerweise müssten ja nun die einzelnen Tabellen "aufgefüllt" werden, sofern der Platz da ist.
D.h. wenn in Best.1 unten, bspw. 5 Zeilen unten frei sind, das diese automatisch aus Best.2 rüber kopiert werden.
Deswegen hatte ich ja gefragt ob es nicht sinnvoller gewesen wenn ich alle Best.1-8 in einer Tabelle
dargestellt hätte.
Auf alle Fälle hast Du dich ja richtig ins Zeug gelegt. Alle Achtung und danke nochmals.
Ich glaube jetzt wird's richtig kompliziert, oder?
VG Thomas
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1713011.html
Geschrieben am: 13.09.2019 22:39:03
Hallo Thomas,
das von Dir geschilderte weitere Problem kann gelöst werden,es ist jedoch mit erheblichem Aufwand verbunden. Ich mache mich an die Arbeit, bitte aber um etwas Geduld. Ich melde mich wieder.
M.f.G. Peter Kloßek
Betrifft: AW: Etiketten konsolidieren
von: 1713149.html
Geschrieben am: 15.09.2019 00:43:56
Hallo Thomas,
also zwischen den verschiedenen Etiketten-Tabellen zu jonglieren, um den freien Platz dort auszunutzen, das wäre wirklich der helle Wahnsinn. Natürlich ist mit Excel (fast) alles machbar, aber der Aufwand wäre durch nichts zu gerechtfertigen.
Ich habe mir daher einen anderen Lösungsweg vorgenommen: Die Daten in den einzelnen "Best.?"-Tabellenblätter werden nach der Eliminierung der Leerzeilen in einem Tabellenblatt "Konsol" konsolidiert oder zusammengefasst. Aus dem Konsol-Blatt werden dann die Etiketten ausgefüllt. Dabei brauchen wir nur ein einziges Etikettenblatt. Ich habe mal dieses Blatt für max. 2 auszudruckende Seiten erstellt/geändert. Die Erweiterung auf weitere Ausdruck-Seiten kann verhältnismäßig einfach durch Kopieren und Ändern der Formeln erfolgen. Bei jedem weiteren Etikett muss in den zuvor kopierten Bereichen dann auf die richtige Zeilennummer (immer fortlaufend) - mit "Suchen und Auswählen" und "Ersetzen" (in Formeln) - Bezug genommen werden. Bitte dabei unbedingt auf die richtigen Zeilenhöhen achten! Man bekommt 10 Tabellenzeilen aus der Konsolidierung auf eine Seite Etiketten. Sind in der Konsolidierung mehr Zeilen enthalten, werden automatisch die weiteren benötigten Blätter angezeigt (Makro 3) oder ausgedruckt (Makro 3a). Auch wenn Du das Etikettenblatt für 10 Seiten einrichtest, wird immer nur diejenige Anzahl Blätter (automatisch) angezeigt/gedruckt, die wirklich nötig sind.
Ich habe auf dem Blatt "Konsol" insgesamt 4 Makrobuttons errichtet. Der 1. Button entspricht dem alten Makro 99 und ist jetzt Makro 4. Wie bei der Konsolidierung wird die Anzahl der Bestell-Blätter automatisch vom Programm ermittelt. Dies erfolgt in der Weise, dass das Programm zunächst die Gesamtzahl der vorhandenen Tabellenblätter (Sheets) ermittelt und dann davon die Anzahl der anderen Tabellenblätter - ich gehe davon aus, dass diese Anzahl nicht variiert - abgezogen wird. Im vorliegenden Fall sind es 3 Tabellenblätter und damit in der Beispielmappe 3 Bestellblätter. Wenn Du weitere Bestellblätter einstellst und die Anzahl der anderen Sheets nicht änderst, ist keine weitere Maßnahme nötig.
Ich habe noch einen kleinen Fehler entdeckt: Nicht nur die Zeile "29" ist hinzuzufügen, sondern darunter auch noch die Zeile "30". Falls die nicht existiert, kommt es dann zu einem Fehler, wenn tatsächlich alle 20 Tabellenzeilen belegt sind und der Button für "Entfernen der Leerzeilen" betätigt wird.
Die Regie-Eintragungen sollten auf den Arbeitsblättern für den Normal-Betrieb ausgeblendet werden. Dazu noch ein Hinweis: Ich verwende beim Blatt "Konsol" zur Zeit eine Obergrenze bis zur Zeile 1000.
Hier nun die ergänzte neue Arbeitsmappe
https://www.herber.de/bbs/user/132042.xlsm
Außerdem habe ich auch die Erläuterungen ergänzt; hierfür diese Datei
https://www.herber.de/bbs/user/132043.xlsm
Soweit noch Fragen Deinerseits bestehen, bitte ich noch heute um Nachricht, da ich ab Montag Renovierungsarbeiten erledigen muss und bis Mittwoch nicht zur Verfügung stehe. Notfalls einen neuen Thread erstellen.
Mit freundlichem Gruß
Peter Kloßek
Betrifft: AW: Etiketten konsolidieren
von: 1713161.html
Geschrieben am: 15.09.2019 10:23:11
Hallo Peter
Ich schaue mir das gleich mal in Ruhe am PC an, sitze momentan am Handy.
Ich hoffe nur dass wir uns jetzt nicht falsch verstanden haben wegen der Etiketten Seiten.
Die Etiketten beziehen ja ihre Informationen aus den einzelnen Zeilen bzw Zellen der Bestellungen.
Das heißt ja, habe ich keine Leerzeilen in den Bestellungen, so entstehen auch keine leeren Etiketten.
Deswegen war es mir ja so wichtig dass die Leerzeilen aufrücken, damit alle Bestellungen generell übersichtlicher ohne leerzeilen werden und eben keine leer Etiketten entstehen.
Aber wie gesagt ich schaue mir das erstmal an. Vielen Dank und bis später liebe Grüße Thomas
Betrifft: AW: Etiketten konsolidieren
von: 1713171.html
Geschrieben am: 15.09.2019 11:57:49
Hallo Peter
Habe nun die neue Variante ausprobiert. Es funzt sehr gut.
Ich werde nun mal meine komplette Tabelle darauf umbauen und schauen wie das Ganze dann funzt.
Das nächste wird sein, wie Du auf dem Fax bereits aufgeführt hast, die Abteilung "Sonstige" hinzubekommen.
Die müssen ja jetzt noch von Hand eingetragen werden.
In den jeweiligen Bestellungen werden zwar die "Sonderwünsche" mit gerechnet, aber landen nicht auf dem Fax.
Da es aber für die Sonderwünsche keine Speziellen Vorgaben gibt, weis ich noch nicht wie man Excel dies beibringt. Excel soll also die variablen Werte (Nussecken, Laubenstange, oder was auch immer)mit der dazugehörigen Anzahl erkennen und im Fax auflisten, ggf. bei gleichen Werten auch addieren.
Aber das wie gesagt später...
Ich sage nochmals ein großes Dankeschön und noch ein schönes Wochenende, sowie viel Erfolg beim renovieren.
LG Thomas
Betrifft: AW: Etiketten konsolidieren u. a.
von: 1713202.html
Geschrieben am: 15.09.2019 15:02:51
Hallo Thomas,
ich denke gerade noch über einige Verbesserungen nach. Bitte warte noch mit der Anpassung Deines Programms.
Die Etikettenkonsolidierung
und das Fax enthält die Daten von sämtlichen Bestellungen. Ist dies so richtig? Oder müssen die einzelnen Bestellungen (Best.1, Best.2, usw.) einzeln per Fax bestellt werden?
Bitte um schnelle Antwort, dann kann ich das Fax evtl. noch mal überarbeiten.
M.f.G. Kloßek
Betrifft: Zeilen löschen mittels Makros?
von: 1713213.html
Geschrieben am: 15.09.2019 16:31:42
Hallo Thomas,
ich habe noch einige Verbesserung auf Lager. In Deiner übermittelten Datei befinden sich Dutzende Makro-Buttons zum Löschen der Tabellenzeilen. Dazugehörende Makros habe ich aber nicht gefunden. Willst Du wirklich so viele Makros erstellen? Das ist nicht nötig. Man kann die Zeilen auch mit einem Rechtsklick mit der Maus auf den jeweiligen Namen löschen. Dazu müssen noch die folgenden Ergänzungen stattfinden.
A. In den Tabellenblättern "Best.1", "Best.2", usw. für alle Best.?-Blätter
1. In Zelle AU10 bitte eintragen: "Sp. d. akt. Zelle". Das Programm trägt dann in AV10 die ausgewählte Spalte ein (wenn richtig ausgewählt ist dies 3 für die Spalte C).
2. Die Spalten "AJ" bis "AK" in dem Tabellenbereich dürfen n i c h t verbunden sein! Bitte neu formatieren.
3. Klicke mit der rechten Maustaste auf das geöffnete Tabellenblatt und wähle "Code anzeigen" im Kontextmenü aus. Ein leeres Code-Blatt wird eingeblendet. Kopiere dann bitte den folgenden Makro hinein:
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Set Target = Intersect(Target, Range("c9:c28"))
If Target Is Nothing Or ActiveCell = 0 Then
Exit Sub
End If
Range("av10") = ActiveCell.Column
If Range("AV10") = 3 Then
Application.CommandBars("cell").Enabled = False
ActiveCell.Offset(0, 0).Range("a1:a1").ClearContents
ActiveCell.Offset(0, 0).Range("c1:af1").ClearContents
ActiveCell.Offset(0, 0).Range("ah1").ClearContents
SendKeys "{ESC}"
Application.OnTime Now + TimeValue("00:00:02"), "Makro5"
End If
End Sub
B. Neues Modul 1 verwenden oder neues Modul einfügen
Wähle "Ansicht", "Makro", "Makro bearbeiten" und dann im Projekt-Explorer Modul 1 aus, oder ein anderes leeres Modul. Ein Modul fügt man ein mit "Einfügen" und "Modul". Hier bitte die folgende Funktion mit dem nachfolgenden Makro 5 einfügen:
PrivateConstVK_NUMLOCK=&H90
PrivateConstKEYEVENTF_EXTENDEDKEY=&H1
PrivateConstKEYEVENTF_KEYUP=&H2
PrivateDeclareSubkeybd_eventLib"user32"_
(ByValbVkAsByte,_
ByValbScanAsByte,_
ByValdwflagsAsLong,_
ByValdwExtraInfoAsLong)
PrivateDeclareFunctionGetKeyboardStateLib"user32"_
(pbKeyStateAsByte)AsLong
PrivateDeclareFunctionSetKeyboardStateLib"user32"_
(lppbKeyStateAsByte)AsLong
FunctionSetNumLock(bStatusAsBoolean)AsBoolean
DimRAsVariant
DimNumLockOnAsBoolean
DimKeyTable(0To255)AsByte
R=GetKeyboardState(KeyTable(0))
NumLockOn=(KeyTable(VK_NUMLOCK)<>0)
If(bStatusAndNotNumLockOn)Or_
(NotbStatusAndNumLockOn)Then
keybd_eventVK_NUMLOCK,_
&H45,_
KEYEVENTF_EXTENDEDKEYOr0,0
keybd_eventVK_NUMLOCK,_
&H45,_
KEYEVENTF_EXTENDEDKEYOrKEYEVENTF_KEYUP,0
EndIf
EndFunction
Sub Makro5()
SetNumLock False
Application.CommandBars("cell").Enabled = True
Range("av10") = 0
End Sub
Bitte mal ausprobieren. Rückmeldung wäre schön.
M.f.G. Kloßek
Betrifft: AW: Zeilen löschen mittels Makros?
von: 1713216.html
Geschrieben am: 15.09.2019 17:04:20
Die Makros hatte ich rausgenommen, sowie Arbeitsmappe etwas abgespeckt.
Die Datei war angeblich zu groß und ich hatte sie nur als XLS und nicht als XLSM verschickt.
Der Gedanke mit den Lösch-Buttons war der, das ich die kleinen Zellen mit den Zwischensummen, sowie die Zeilensumme am Ende, gesperrt hatte und dann nur die Zellen die relevant waren, zu löschen.(Name, Brötchenanzahl und Anmerkungen)
Betrifft: AW: Etiketten konsolidieren u. a.
von: 1713215.html
Geschrieben am: 15.09.2019 16:56:41
Hallo Peter
Ich glaube ich zerhacke mir hier grad alles...
Ich habe ja in den Bestellungen die Zellen mit den kleinen Zwischensummen(hinter den Brötchenanzahlen)rausgelöscht. habe dann Deine Zeilenberechnung übernommen.
Natürlich stimmen jetzt die Zellbezeichnung im Makro1 auch nicht mehr überein.
Irgendwie klappt das grad nicht mit der Änderung.
Auf dem Fax sollen natürlich die gesamten Bestellungen aufgeführt sein, das macht es ja auch.
Es werden ja nur die Summen aus den einzelnen Bestellungen (Zeile30, je Sorte)addiert.
Also ist es dann egal ob man 1,3 oder 8 Seiten ausfüllt.
Ich sehe grad Du hast ja schon die nächste Nachricht geschickt.
Ich finde es sehr gewöhnungsbedürftig hier im Forum auf die Beiträge zu antworten.
Zumal ich nicht mitbekomme wann die Nachrichten eintreffen.
Siehst Du evtl. ein Möglichkeit direkt per Mail in Kontakt zu treten, ggf. dann auch tel. ?
LG Thomas
Betrifft: Weitere Probleme
von: 1713218.html
Geschrieben am: 15.09.2019 18:32:09
Hallo Thomas,
mit dem "Zeilenlöschen" ist das sehr gefährlich! Hier liegt der Teufel im Detail. Es sind 3 Bereiche, die in einer Tabellenzeile (von 1-20) gesondert behandelt werden müssen. Die Formeln und die Personennummern sollen ja bleiben. Du kannst übrigens auf meinen Beitrag (Etiketten konsolidieren) im Forum zurückgreifen, um eine funktionierende Datei zu erhalten. Die von Dir erstellten Makros kannst Du aus Deiner alten Datei auf das Modul 1 oder ein anderes Modul übertragen - aber bitte nichts mit Zeilenlöschen. Die von mir gewählten Blatt-Namen sollten erhalten bleiben, ansonsten müssten Änderungen in den Makros erfolgen. Die Frage ist, ob überhaupt noch zusätzliche (zu den von mir erstellten) Makros notwendig sind. Schildere mir doch mal einfach, zu welchen noch gewünschten Funktionen Makros fehlen.
In wenigen Tagen läuft dieser Thread ab. Um die Kommunikation zu vereinfachen, teile ich Dir gerne meine E-Mail Adresse mit: peter.klossek(at)online.de.
Also, jetzt weiß ich endlich, dass alle Brötchen - aus allen Best.?-Dateien - per Fax bestellt werden sollen. Es sind also "nur noch" die sonstigen Brötchen aufzulisten. Ich mache mich nochmal an die Arbeit. Wenn ich soweit bin, schicke ich Dir die komplette Ausfertigung nochmals zu.
M.f.G. Kloßek
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1712766.html
Geschrieben am: 12.09.2019 19:32:34
Hallo MM
Auch Dir vielen Dank für Deine Mühe.
Generell funzt den Makro schon, aber es schiebt die Tabelle zusammen.
D.h. je mehr leere Zeilen ich lösche, umso kleiner wird die Tabelle.
Oder mache ich was falsch ?
VG Thomas
Betrifft: AW: Zeile automatisch hoch kopieren
von: 1712953.html
Geschrieben am: 13.09.2019 15:30:24
Hallo Thomas,
nein, du machst nichts falsch, das ist so. Dass die Fußzeile auf Ihrer Position gehalten werden soll, war nicht Bestandteil der Anforderung :-)
Im Zweifel, Ändere die Zeile:
If Trim(Cells(r, c)) = "" Then Rows(29).Rows.Insert: Rows(r).Delete